MySQL触发器触发器是一个特殊的存储过程，不同的是，执行存储过程要使用CALL语句来调用，而触发器的执行不需要使用CALL语句来调用,也不需要手工启动,只要当一个预定义的事件发生的时候，就会被MYSQL自动调用
mysql触发器
修改delimiter为//
mysql> \d //
创建一个名字为tg1的触发器,当向t1表中插入数据时，就向t2表中插入一条数据
mysql> create trigger tg1 before inserton t1 for each row
>begin
>insert into t2(id) values(new.id);
>end//
--准备两个空表t1和t2
mysql> select * from t1;
mysql> select * from t2;
--向t1表中插入多条数据:
mysql> insert into t1values(1),(2),(3),(4);
如何制作删除表t1后t2表中的记录也会跟着删除呢
mysql>\d //
mysql> create trigger tg2 beforedeleteon t1 for each row
>begin delete from t2 where id=old.id;
>end//
mysql>\d ;
如何制作更改表t1后t2表中的记录跟着个性呢
mysql>\d //
mysql> create trigger tg3 beforeupdateon t1 for each row
>begin update t2 set id=new.id whereid=old.id;
>end//
mysql>\d ;
查看触发器
mysql> show triggers;
创建触发器：
创建只有一个执行执行语句的触发器：
create trigger trigger_name trigger_time trigger_event
on tb1_name for each rowtrigger_stmt
trigger_time:before 和 after (before:插入操作之前，after:更新操作之后)
trigger_event:insert,update,delete
tb1_name:标识建立触发器的表名
trigger_stmt:触发器程序体,以begin和end作为开始和结束
eg:
mysql> create trigger ins_sum before insert on account for each row set@sum=@sum+NEW.amount;
mysql> set @sum=0;
mysql> insert into account values('1','10');
mysql> select @sum;
+-------+
| @sum |
+-------+
| 10.00 |
+-------+
创建有多个执行语句的触发器
create trigger trigger_name trigger_time trigger_event
on tb1_name for each row trigger_stmt
eg:
mysql> create trigger testref before insert on test1
-> for each row begin
-> insert into test2 seta2=NEW.a1;
-> delete from test3 wherea3=NEW.a1;
-> update test4 set b4=b4+1where a4=NEW.a1;
-> end
-> //
Query OK, 0 rows affected (0.01 sec)
查看触发器：
1.show triggers\G
eg:
mysql> show triggers\G;
*************************** 1. row ***************************
Trigger: ins_sum
Event: INSERT
Table: account
Statement: set@sum=@sum+NEW.amount
Timing: BEFORE
Created: NULL
sql_mode:
Definer:root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation:utf8_general_ci
2.通过information_schema表查看
mysql> select * from information_schema.triggers wheretrigger_name='testref'\G;
*************************** 1. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA:triggers
TRIGGER_NAME:testref
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA:triggers
EVENT_OBJECT_TABLE: test1
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: begin
insert into test2 set a2=NEW.a1;
delete from test3 where a3=NEW.a1;
update test4 set b4=b4+1 where a4=NEW.a1;
end
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE:
DEFINER:root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION:utf8_general_ci
DATABASE_COLLATION:utf8_general_ci
1 row in set (0.97 sec)
3.查看所有的触发器：
mysql> select * from information_schema.triggers\G;
删除触发器：
Drop trigger [schema_name,] trigger_name
eg:
mysql> drop trigger trig_insert;
Query OK, 0 rows affected (0.01 sec)
触发器（TRIGGER）是由事件来触发某个事件，这些事件包括insert,update,delete语句等
mysql>\d //
创建一个名字为tg1的触发器，当向t1表中插入数据时，就向t2表中插入一条数据
mysql>create trigger tg1 before insert on t1 for each row
>begin
>insert into t2(id) values(new.id); //insert into t2(id,name)values(new.id,new.name);
>end//
准备两个空表t1和t2
mysql>select * from t1;
mysql>select * from t2;
向t1表中插入多条数据;
mysql>insert into t1 values(1),(2),(3),(4);
如何制作删除表t1后t2表中的记录也会跟着删除呢？
mysql>\d //
mysql>create trigger tg2 before delete on t1 for each row
>begin
>delete from t2 where id=old.id; //old.id，是表原来有数据
>end//
如何制作更改表t1后t2表中的记录跟着更改呢?
mysql>\d //
mysql>create trigger tg3 before update on t1 for each row
>begin update t2 set id=new.id where id=old.id; //new.id是更新前原表没有数据
>end//
mysql>\d ;
查看触发器：
mysql>show triggers;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
创建触发器
语法；
create trigger 触发器名 before|after 触发事件 ON 表名 for each row 执行语句
只执行一个语句：
mysql> create trigger dept_trig1 before insert on department for eachrow insert into trigger_time values(NOW());
trigger_time表要事先创建
执行多个语句的触发器:
语法：
create trigger 触发器名 before|after 触发事件 ON 表名 for each row
begin
执行语名列表
end
解决；问题
delimiter &&可以将结束符号变成&&
delimiter ;将结束符号变成;
\d ;
\d //
eg:
delimiter &&
create trigger dept_trig2 afterdelete
on department for each row
begin
insert into trigger_timevalues('21:02:02');
insert into trigger_timevalues('22:02:01');
end
&&
delimiter ;
查看触发器
所有的触发器的信息都存储在information_schema数据库下的triggers表中
1.mysql> show triggers \G ;
*************************** 1. row ***************************
Trigger: dept_trig1
Event: INSERT
Table: department
Statement: insert into trigger_time values(now())
Timing: BEFORE
Created: NULL
sql_mode:
Definer: root@localhost
*************************** 2. row ***************************
Trigger: dept_trig3
Event: DELETE
Table: department
Statement: begin
insert into trigger_time values('21:01:01');
insert into trigger_time values('22:02:02');
end
Timing: AFTER
Created: NULL
sql_mode:
Definer: root@localhost
2 rows in set (0.00 sec)
ERROR:
No query specified
2.在triggers表中查看触发器
mysql> select * from information_schema.triggers\G;
*************************** 1. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA:company
TRIGGER_NAME:dept_trig1
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA:company
EVENT_OBJECT_TABLE:department
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: insertinto trigger_time values(now())
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE:
DEFINER:root@localhost
*************************** 2. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA:company
TRIGGER_NAME:dept_trig3
EVENT_MANIPULATION: DELETE
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA:company
EVENT_OBJECT_TABLE:department
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: begin
insert into trigger_time values('21:01:01');
insert into trigger_time values('22:02:02');
end
ACTION_ORIENTATION: ROW
ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE:
DEFINER:root@localhost
2 rows in set (0.01 sec)
ERROR:
No query specified
显示指定的trigger触发器的信息
select * from information_schema.triggers where trigger_name='触发器名';
mysql> select * from information_schema.triggers wheretrigger_name='dept_trig1'\G;
*************************** 1. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA:company
TRIGGER_NAME:dept_trig1
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA:company
EVENT_OBJECT_TABLE:department
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: insertinto trigger_time values(now())
ACTION_ORIENTATION: ROW
ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE:
DEFINER:root@localhost
1 row in set (0.01 sec)
触发器的使用
在myslq中,触发器执行的顺序是
1. before 触发器
2.表操作(insert,update,delete)
3.after触发器
删除触发器
mysql> drop trigger dept_trig1;
Query OK, 0 rows affected (0.00 sec)
