MySQL |
您所在的位置：网站首页 › mysql的触发器怎么用 › MySQL |
MySQL
|
触发器的作用是当表上有对应SQL语句发生时，则触发执行。 1.语法 CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW [trigger_order] trigger_body trigger_time: { BEFORE | AFTER } trigger_event: { INSERT | UPDATE | DELETE } trigger_order: { FOLLOWS | PRECEDES } other_trigger_name说明： trigger_name：触发器的名称，不能与已经存在的触发器重复；trigger_time：{ BEFORE | AFTER }，表示在事件之前或之后触发；trigger_event:：{ INSERT |UPDATE | DELETE }，触发该触发器的具体事件；tbl_name：该触发器作用在tbl_name上；2.关键说明 Definer关键词用来指定trigger的安全环境；trigger_time指定触发器的执行时间，BEFORE和AFTER指定触发器在表中的每行数据修改前或者后执行；trigger_event指定触发该触发器的集体事件： INSERT 当新的一行数据插入表中时触发，如执行insert，load data，replace语句插入新数据 UPDATE 当表的一行数据被修改时触发，如执行update语句时 DELETE 当表的一行数据被删除时触发，如执行delete，replace语句时当执行insert into...on duplicate key update语句时，当碰到重复行执行update时，则触发update下的触发器；从5.7.2版本开始，可以创建具有相同trigger_time和trigger_event的同一个表上的多个触发器，默认情况下，按照创建时间依次执行，通过指定FOLLOWS/PRECEDES改变执行顺序，即FOLLOWS时表示新创建的触发器执行，PRECEDES则表示新触发器先执行；trigger_body 表示触发器触发之后要执行的一个或多个语句，在内部可以引用涉及表的字段，OLD.col_name表示行数据被修改或删除之前的字段数据，NEW.col_name表示数据被插入或修改之后的字段数据；3.实例 mysql> delimiter // mysql> create trigger simple_trigger -> after update -> on students for each row -> begin -> insert into students_bak values(old.sid,old.sname,new.sname,old.gender,new.gender,now()); -> end// Query OK, 0 rows affected (0.16 sec) mysql> select * from students; +-----+--------+--------+---------+ | sid | sname | gender | dept_id | +-----+--------+--------+---------+ | 1 | Andrew | -1 | 1 | | 2 | Andy | -1 | 1 | | 3 | Bob | -1 | 1 | | 4 | Ruth | -1 | 2 | | 5 | Mike | -1 | 2 | | 6 | John | 0 | 3 | | 7 | Cindy | 1 | 3 | | 8 | Susan | 1 | 3 | +-----+--------+--------+---------+ 8 rows in set (0.01 sec) mysql> update students set sname='abc',gender=1 where sid=1; Query OK, 1 row affected (0.03 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from students; +-----+-------+--------+---------+ | sid | sname | gender | dept_id | +-----+-------+--------+---------+ | 1 | abc | 1 | 1 | | 2 | Andy | -1 | 1 | | 3 | Bob | -1 | 1 | | 4 | Ruth | -1 | 2 | | 5 | Mike | -1 | 2 | | 6 | John | 0 | 3 | | 7 | Cindy | 1 | 3 | | 8 | Susan | 1 | 3 | +-----+-------+--------+---------+ 8 rows in set (0.00 sec) mysql> select * from students_bak; +--------+----------+----------+-----------+-----------+---------------------+ | oldsid | oldsname | newsname | oldgender | newgender | tstamp | +--------+----------+----------+-----------+-----------+---------------------+ | 1 | Andrew | abc | -1 | 1 | 2017-12-13 11:04:52 | +--------+----------+----------+-----------+-----------+---------------------+ 1 row in set (0.00 sec)定义一个trigger的时候，要用到delimiter来定义一个结束的标识符，方便编写语句。执行完毕后，当前会话的所有的语句结束符都变成了 // ，所有创建完了，要退出，从新连接mysql恢复默认分号（;）结束符。
4.查看触发器（information_schema） mysql> select TRIGGER_SCHEMA,TRIGGER_NAME,DEFINER,ACTION_STATEMENT from TRIGGERS where TRIGGER_SCHEMA='course'; +----------------+----------------+----------------+-----------------------------------------------------------------------------------------------------+ | TRIGGER_SCHEMA | TRIGGER_NAME | DEFINER | ACTION_STATEMENT | +----------------+----------------+----------------+-----------------------------------------------------------------------------------------------------+ | course | simple_trigger | root@localhost | begin insert into students_bak values(old.sid,old.sname,new.sname,old.gender,new.gender,now()); end | +----------------+----------------+----------------+-----------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)5.删除触发器 DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name if exists用来避免删除不存在的触发器时引发的报错当你执行drop table时，表上的触发器也被drop掉了 mysql> drop trigger simple_trigger; Query OK, 0 rows affected (0.00 sec) |
【本文地址】
今日新闻 |
推荐新闻 |