MySQL/MariaDB触发器
本文目录:
1.创建触发器
2.insert触发器
3.delete触发器
4.update触发器
5.通过on duplicate key update分析触发器触发原理
6.replace to算法验证
7.查看、删除触发器
触发器用来实现在永久表上进行某些操作时触发启动另一操作。
1.创建触发器
以下是MariaDB中create trigger的语法:mysql不支持or replace和if not exists子句。
CREATE [OR REPLACE] TRIGGER [IF NOT EXISTS] trigger_name { BEFORE | AFTER } { INSERT | UPDATE | DELETE } ON tbl_name FOR EACH ROW trigger_body
触发器只能建立在永久表上,不能建立在视图和临时表上。MySQL/MariaDB中的触发器只支持行级触发器(即每行都触发一次触发器),不支持数据库级别和服务器级别的触发器。MySQL/MariaDB中的触发器虽然都是基于表的,却存储在数据库下,理解这一点很重要,以后查看、删除、引用trigger的时候都是通过数据库名称来引用的,而不是使用表来引用。
before和after是触发时间,insert/update/delete是触发事件。例如before insert
表示插入记录之前触发程序。其中before触发器类似于SQL Server中的instead of触发器,作用在检查约束之前。而after触发器和SQL Server中一样,在检查约束之后才生效。
下图为SQL Server中instead of和after触发器的工作位置。在MySQL/MariaDB中是一样的,只要把MySQL/MariaDB中的概念和SQL Server中的概念对应起来即可。后文中有对该图的分析。
在MySQL中,一张表只能有一个同时间、同事件的触发器,所以MySQL中不支持基于列的触发器。例如,一张表中可以存在before insert
触发器和before update
,所以每张表最多只能有6个触发器。但是MariaDB 10.2.3中可以为同时间、同事件创建多个触发器。
在MySQL/MariaDB中,使用old和new表分别表示触发器激活后的新旧表,在SQL Server中使用的是inserted和deleted表,其实它们的意义是等价的。但是坑爹的是MySQL/MariaDB中只能引用这两张表中的列,而无法直接引用这两张表。例如可以引用old.col_name
,但是不能直接select * from old
这样引用old表。
old表表示删除目标记录之后将删除的记录保存在old表中,即deleted表。new表表示向表中插入新记录之前,新记录保存在new表中,即inserted表。或者说,只要涉及了insert相关的操作就有new表,只要涉及了delete相关的操作就有old表,而update操作基本可以认为是先delete再insert的行为,所以也会触发这两张表。
注意,即使是after触发器,也是先将数据填充到old、new表中,再执行DML语句,最后激活触发器执行触发器中的语句。
在下面的小节中会分别验证不同事件不同时间的触发器行为。在验证它们之前,先创建示例数据。
CREATE DATABASE IF NOT EXISTS test ; USE test ; CREATE OR REPLACE TABLE emp ( emp_no INT (11) NOT NULL, mgr_no INT (11) DEFAULT NULL, emp_name VARCHAR (30) DEFAULT NULL, PRIMARY KEY (emp_no) ) INSERT INTO emp (emp_no, mgr_no, emp_name) VALUES (1, NULL, 'David'), (2, 3, 'Mariah'), (3, 1, 'Tommy'), (4, 1, 'Jim'), (5, 3, 'Selina'), (6, 4, 'John'), (8, 3, 'Monty');
查看该表数据。
再创建一个极其简单的审核表audit,该表前两列为自增列和注释列,后面的列结构等同于emp表。
DROP TABLE IF EXISTS audit; CREATE TABLE audit AS SELECT * FROM emp WHERE 1=0; ALTER TABLE audit ADD id INT AUTO_INCREMENT PRIMARY KEY FIRST; ALTER TABLE audit ADD note CHAR(50) AFTER id;
2.insert触发器
insert触发器的作用是:当向表中插入数据的时候,将会激活触发器。有两类:before和after触发器,分别表示数据插入到表中之前和数据插入到表中之后激活触发器。
注意,只要向表中插入了新行,就会激活insert触发器。插入新行的动作不仅仅只有insert语句,还有其他插入操作,例如load data语句、replace语句等等。
# 创建before insert触发器 DELIMITER $$ CREATE OR REPLACE TRIGGER test.trig_demo1 BEFORE INSERT ON test.emp FOR EACH ROW BEGIN INSERT INTO audit VALUES(null,'before insert',new.emp_no,new.mgr_no,new.emp_name); END$$ DELIMITER ; # 创建after insert触发器 DELIMITER $$ CREATE OR REPLACE TRIGGER test.trig_demo2 AFTER INSERT ON test.emp FOR EACH ROW BEGIN INSERT INTO audit VALUES(null,'after insert',new.emp_no,new.mgr_no,new.emp_name); END$$ DELIMITER ;
before insert
触发器的作用是:当向表emp中insert数据时,将首先激活该触发器,该触发器首先会将待插入数据填充到new表中,再向审核表audit中插入一行数据,并标明此次触发操作是"before insert"。触发器执行结束后,才开始向emp表中插入数据。
after insert
触发器的作用是:当向表emp中insert数据时,将先将数据填充到new表中,再插入到emp表,之后激活该触发器,该触发器会向审核表audit中插入一行数据,并标明此次触发操作是"after insert"。
现在向emp表中插入数据进行测试。
INSERT INTO emp VALUES(10,3,'longshuai');
插入之后,查看audit表。
MariaDB [test]> select * from audit; +----+---------------+--------+--------+-----------+ | id | note | emp_no | mgr_no | emp_name | +----+---------------+--------+--------+-----------+ | 1 | before insert | 10 | 3 | longshuai | | 2 | after insert | 10 | 3 | longshuai | +----+---------------+--------+--------+-----------+
可以看到,一次insert操作触发了before insert和after insert两个触发器。且无论是before还是after insert触发器都有new表的存在。
在mariadb 10.2.3版本之后,一个表中可以为同一时间、同一事件创建多个触发器(在mysql中不允许)。例如:
# 创建第二个after insert触发器 DELIMITER $$ CREATE OR REPLACE TRIGGER test.trig_demo3 AFTER INSERT ON test.emp FOR EACH ROW BEGIN INSERT INTO audit VALUES(null,'after insert2',new.emp_no,new.mgr_no,new.emp_name); END$$ DELIMITER ;
show triggers;
此处删除新建的这个trigger,注意删除trigger的时候是通过数据库名称来也引用trigger的,而不是table名称。
drop trigger test.trig_demo3;
3.delete触发器
delete触发器的作用是:当删除表中数据记录的时候,将会激活触发器。
有两类insert触发器:before和after触发器,分别表示表中记录被删除之前和表中数据被删除之后激活触发器。
注意,delete触发器只在表中记录被删除的时候才会被激活。例如delete语句、replace语句。但是drop语句、truncate语句不会激活delete触发器,因为它们是DDL语句,而MySQL/MariaDB不支持DDL触发器,它们并没有对表中的记录执行delete操作。
# 创建before delete触发器 DELIMITER $$ CREATE OR REPLACE TRIGGER test.trig_demo3 BEFORE DELETE ON test.emp FOR EACH ROW BEGIN INSERT INTO audit VALUES(NULL,'before delete',old.emp_no,old.mgr_no,old.emp_name); END$$ DELIMITER ; # 创建after delete触发器 DELIMITER $$ CREATE OR REPLACE TRIGGER test.trig_demo4 AFTER DELETE ON test.emp FOR EACH ROW BEGIN INSERT INTO audit VALUES(NULL,'after delete',old.emp_no,old.mgr_no,old.emp_name); END$$ DELIMITER ;
这两个delete事件的触发器作用很简单,先将待删除的记录插入到old表中,再在删除表中的记录之前、之后,向审核表audit中插入一行'before delete'或'after delete'的审核日志。
现在删除emp表中的一行记录进行测试。
delete from emp where emp_no=10;
删除emp表中数据之后,查看audit表。
MariaDB [test]> SELECT * FROM audit; +----+---------------+--------+--------+-----------+ | id | note | emp_no | mgr_no | emp_name | +----+---------------+--------+--------+-----------+ | 1 | before insert | 10 | 3 | longshuai | | 2 | after insert | 10 | 3 | longshuai | | 3 | before delete | 0 | NULL | NULL | | 4 | after delete | 0 | NULL | NULL | +----+---------------+--------+--------+-----------+
可见,一次delete操作触发了before delete和after delete触发器。且删除记录前后old表都存在。
4.update触发器
update触发器的作用是:当表中数据记录被修改的时候,将会激活触发器。
有两类update触发器:before和after触发器,分别表示表中记录被修改之前和表中数据被修改之后激活触发器。
注意,update操作可以认为是先delete再insert,因此它将填充old表和new表。
# 创建before update触发器 DELIMITER $$ CREATE OR REPLACE TRIGGER test.trig_demo5 BEFORE UPDATE ON test.emp FOR EACH ROW BEGIN INSERT INTO audit VALUES(NULL,'before update from new',new.emp_no,new.mgr_no,new.emp_name); INSERT INTO audit VALUES(NULL,'before update from old',old.emp_no,old.mgr_no,old.emp_name); END$$ DELIMITER ; # 创建after update触发器 DELIMITER $$ CREATE OR REPLACE TRIGGER test.trig_demo6 AFTER UPDATE ON test.emp FOR EACH ROW BEGIN INSERT INTO audit VALUES(NULL,'after update from new',new.emp_no,new.mgr_no,new.emp_name); INSERT INTO audit VALUES(NULL,'after update from old',old.emp_no,old.mgr_no,old.emp_name); END$$ DELIMITER ;
before update
触发器的作用是:当更新emp表中的一条记录时,首先将表中该行记录插入到old表中,待更新结果插入到new表中,然后激活触发器,向审核表中写入数据,最后修改emp表中的记录。after update
触发器的作用是:当更新emp表中的一条记录时,首先将表中该行记录插入到old表中,待更新结果插入到new表中,然后修改emp表中的记录,最后激活触发器,向审核表中写入数据。
更新emp表中一行记录。
update emp set emp_no=7 where emp_no=8;
查看audit表。
MariaDB [test]> select * from audit; +----+------------------------+--------+--------+-----------+ | id | note | emp_no | mgr_no | emp_name | +----+------------------------+--------+--------+-----------+ | 1 | before insert | 10 | 3 | longshuai | | 2 | after insert | 10 | 3 | longshuai | | 3 | before delete | 0 | NULL | NULL | | 4 | after delete | 0 | NULL | NULL | | 5 | before update from new | 7 | 3 | Monty | | 6 | before update from old | 8 | 3 | Monty | | 7 | after update from new | 7 | 3 | Monty | | 8 | after update from old | 8 | 3 | Monty | +----+------------------------+--------+--------+-----------+
可以看到,一次update操作触发了before update触发器和after update触发器,并且update操作时,new和old两张表中都有新旧数据。上面的结果中from new对应的是更新后的数据,来源于更新前填充的new表,from old对应的是更新前的旧数据,来源于更新前填充的old表。
5.通过on duplicate key update分析触发器触发原理
在MySQL/MariaDB中,如果向表中插入的数据有重复冲突检测时会阻止插入。解决这个问题的其中一个方法就是使用on duplicate key update
子句。这个子句应用在insert字句中,但其中涉及到了update操作,那到底会触发哪些触发器呢?
这里先清空上面的audit表。
TRUNCATE audit;
首先测试下使用on duplicate key update
子句插入无重复的记录。注意,emp表的emp_no列具有主键属性,它不允许出现重复值。
INSERT INTO emp VALUES(15,5,'xiaofang') ON DUPLICATE KEY UPDATE emp_name='xiaofang';
查看audit表。
MariaDB [test]> select * from audit; +----+---------------+--------+--------+----------+ | id | note | emp_no | mgr_no | emp_name | +----+---------------+--------+--------+----------+ | 1 | before insert | 15 | 5 | xiaofang | | 2 | after insert | 15 | 5 | xiaofang | +----+---------------+--------+--------+----------+
可以看到,在插入没有重复冲突的行只触发了before insert和after insert触发器。没有触发update触发器。
再插入一条有重复冲突的记录。
TRUNCATE audit; INSERT INTO emp VALUES(3,1,'xiaofang') ON DUPLICATE KEY UPDATE emp_name='xiaofang';
查看audit表:
MariaDB [test]> select * from audit; +----+------------------------+--------+--------+----------+ | id | note | emp_no | mgr_no | emp_name | +----+------------------------+--------+--------+----------+ | 1 | before insert | 3 | 1 | xiaofang | | 2 | before update from new | 3 | 1 | xiaofang | | 3 | before update from old | 3 | 1 | Tommy | | 4 | after update from new | 3 | 1 | xiaofang | | 5 | after update from old | 3 | 1 | Tommy | +----+------------------------+--------+--------+----------+
可以看到,这里触发了3个触发器:before insert/before update/after update,为什么前面只触发了两个insert触发器而这里触发了3个触发器。其实根据下面的图很好分析。
在insert into... on duplicate key update
语句中,插入没有重复值冲突的记录时,首先判断是否存在before insert触发器,有就触发,触发之后检查约束,发现没有重复值冲突,然后直接触发after insert触发器。所以这种情况下只触发了before insert和after insert触发器。
而插入有重复值冲突的记录时,首先触发了before insert触发器,然后检查约束发现存在重复值冲突,所以改insert操作为update操作,update操作再次回到事务的顶端,先触发before update再检查约束,这时候已经不再重复值冲突,所以后面触发after update触发器。
6.replace to算法验证
插入新记录时,对于重复值冲突的记录,使用replace to
语句代替insert into是另一种方法。这种方法实现方式和on duplicate key update
方式不一样。
replace to算法说明如下:
- 尝试插入新行。
- 存在重复值冲突时,从表中删除重复行。
- 将新行插入到表中。
也就是说,存在重复值冲突时,如果使用触发器的话,将先触发before insert,再触发delete操作,先是before delete再是after delete,最后触发after insert。
以下是验证过程和结果:首先清空audit表,再插入重复冲突的记录。
TRUNCATE audit; REPLACE INTO emp VALUES(3,1,'gaoxiaofang');
查看audit表:
MariaDB [test]> select * from audit; +----+---------------+--------+--------+-------------+ | id | note | emp_no | mgr_no | emp_name | +----+---------------+--------+--------+-------------+ | 1 | before insert | 3 | 1 | gaoxiaofang | | 2 | before delete | 0 | NULL | NULL | | 3 | after delete | 0 | NULL | NULL | | 4 | after insert | 3 | 1 | gaoxiaofang | +----+---------------+--------+--------+-------------+
显然,和算法说明的结果是对应的。
7.查看、删除触发器
mysql> SHOW CREATE TRIGGER trig_demo5\G *************************** 1. row *************************** Trigger: trig_demo5 sql_mode: SQL Original Statement: CREATE DEFINER=`root`@`192.168.100.%` TRIGGER `test`.`trig_demo5` BEFORE UPDATE ON `test`.`emp` FOR EACH ROW BEGIN INSERT INTO audit VALUES(NULL,'before update from new',new.emp_no,new.mgr_no,new.emp_name); INSERT INTO audit VALUES(NULL,'before update from old',old.emp_no,old.mgr_no,old.emp_name); END character_set_client: utf8 collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci
mysql> show triggers; mysql> show trigger like 'pattern'; mysql> show trigger where 'expression';
但是要注意,这个like的模式是对表名进行匹配的,而不是触发器名。例如触发器trig_demo1是基于emp表创建的,则使用like 'emp'而不能使用like 'trig_demo1'。
在information_schema中有TRIGGERS元数据表:
例如:
mysql> select * from information_schema.triggers where trigger_name='trig_demo1'\G *************************** 1. row *************************** TRIGGER_CATALOG: def TRIGGER_SCHEMA: test TRIGGER_NAME: trig_demo1 EVENT_MANIPULATION: INSERT EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: test EVENT_OBJECT_TABLE: emp ACTION_ORDER: 0 ACTION_CONDITION: NULL ACTION_STATEMENT: BEGIN INSERT INTO audit VALUES(null,'before insert',NEW.emp_no,new.mgr_no,new.emp_name); 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@192.168.100.% CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: latin1_swedish_ci 1 row in set (0.00 sec)
删除触发器的时候,需要使用drop语句指定数据库名,而不是指定表名称。例如:
DROP TRIGGER [ IF EXISTS ] test.example_trigger;
回到Linux系列文章大纲:http://www.cnblogs.com/f-ck-need-u/p/7048359.html
回到网站架构系列文章大纲:http://www.cnblogs.com/f-ck-need-u/p/7576137.html
回到数据库系列文章大纲:http://www.cnblogs.com/f-ck-need-u/p/7586194.html
转载请注明出处:http://www.cnblogs.com/f-ck-need-u/p/8870446.html
注:若您觉得这篇文章还不错请点击右下角推荐,您的支持能激发作者更大的写作热情,非常感谢!
上一篇: 她说,晚上睡觉一个人害怕
下一篇: 恼透了挑大粪的农夫