深入浅出mysql_触发器

十三. 触发器


13.1 创建触发器


语法：
CREATE TRIGGER trigger_name {BEFORE|AFTER} trigger_event ON table_name FOR EACH ROW trigger_stmt


对于INSERT INTO .... ON DUPLICATE KEY UPDATE 触发顺序


语法


CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt


 trigger_time： 是触发器的触发时间，可以是 BEFORE 或者 AFTER，BEFORE 的含义指在检查约束前触发，而 AFTER 是在检查约束后触发。
 trigger_event ：就是触发器的触发事件，可以是 INSERT、UPDATE 或者 DELETE。


范例


为 film 表创建了 AFTER INSERT 的触发器，具体如下：
DELIMITER $$
CREATE TRIGGER ins_film
AFTER INSERT ON film FOR EACH ROW BEGIN
INSERT INTO film_text (film_id, title, description)
VALUES (new.film_id, new.title, new.description);
END;
$$
delimiter ;


 INSERT INOT…ON DUPLICATE KEY UPDATE语句，触发触发器的顺序


对于有重复记录，需要进行 UPDATE 操作的 INSERT，触发器触发的顺序是 BEFORE INSERT、BEFORE UPDATE、AFTER UPDATE；对于没有重复记录的 INSERT，就
是简单的执行 INSERT 操作，触发器触发的顺序是 BEFORE INSERT、AFTERINSERT。对于那些实际执行 UPDATE 操作的记录，仍然会执行 BEFORE INSERT 触发器的内容，在设计触发器的时候一定要考虑这种情况，避免错误地触发了触发器。


范例


mysql> INSERT INTO film VALUES
-> (1002,'Only test',
-> 'Only test',2006,1,NULL,6,'0.99',86,'20.99','PG',
-> 'Deleted Scenes,Behind the Scenes','2006-02-15 05:03:42')
-> ON DUPLICATE KEY
-> UPDATE title='update record';


 tips


1. 一个表 相同的触发时间的相同触发事件只能创建一个触发器
2. 触发器只能创建在永久表（Permanent Table）上，不能对临时表（Temporary Table）创建触发器。


13.2 删除触发器


一次可以删除一个触发程序，如果没有指定 schema_name，默认为当前数据库


语法


DROP TRIGGER [schema_name.]trigger_name 


范例


要删除 film 表上的触发器 ins_film，可以使用以下命令：
mysql> drop trigger ins_film;
Query OK, 0 rows affected (0.00 sec)


13.3 查看触发器


SHOW TRIGGERS


范例


mysql> show triggers \G
*************************** 1. row ***************************
Trigger: customer_create_date
Event: INSERT
Table: customer
Statement: SET NEW.create_date = NOW()
Timing: BEFORE
Created: NULL
sql_mode:
STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZER
O,TRADITIONAL,NO_AUTO_CREATE_USER
Definer: root@localhost
*************************** 2. row ***************************
……


查询系统表的 information_schema.triggers 表


范例


mysql> select * from triggers where trigger_name = 'ins_film_bef' \G
*************************** 1. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA: sakila
TRIGGER_NAME: ins_film_bef
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA: sakila
EVENT_OBJECT_TABLE: film
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN
INSERT INTO tri_demo (note) VALUES ('before insert');
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
1 row in set (0.01 sec)


13.4 触发器的使用


触发器执行的语句限制： 


1.触发程序不能调用将数据返回客户端的存储程序，也不能采用CALL语句的动态sql语句，但是允许存储过程通过参数将数据返回给触发程序，也就是存储过程或者函数通过OUT 或者INOUT 类型的参数将数据返回触发器
2.不能在触发器中使用以显式或者隐式方式开始或者结束语句，如START TRANSACTION、 COMMIT、ROLLBACK；


MySQL 的触发器是按照 BEFORE 触发器、行操作、AFTER 触发器的顺序执行的，其中任何一步操作发生错误都不会继续执行剩下的操作。如果是对事务表进行的操作，那么会整个作为一个事务被回滚（Rollback），但是如果是对非事务表进行的操作，那么已经更新的记录将无法回滚，这也是设计触发器的时候需要注意的问题