触发器
概念
属于一个特殊的存储过程,属于嵌入到mysql的一段程序,触发器有触发事件来接触,触发事件包含(insert、update、delete),一旦触发触发器,数据则会执行触发器中预先设置好的内容,不需要手动执行,整个过程全部自动完成
触发器属于隐性触发的过程,可能会因为触发触发器而造成的数据表的数据修改,而造成的与原数据不匹配的问题,所以生产环境中将会使用存储过程去替代触发器完成各项任务,在一个表中只能出现两个触发器,且触发时机一个为before,一个为after
创建触发器的语法
CREATE TRIGGER 触发器名 触发时机(before和after) 触发事件(insert/update/delete) ON 表名 FOR EACH ROW [BEGIN] [END]
触发器中如果有多个触发事件,需要用到begin和end,begin和end之间是触发后执行的命令
before和after都用于标识触发事件,before表示触发器中的命令在dml语句修改数据之前执行,after则表示触发器中的命令在dml语句修改数据之后执行
例:单表触发单条语句
mysql> CREATE TABLE student1(name VARCHAR(30),grade DECIMAL(8,2)); /*创建测试表*/
mysql> SET @sum=0; /*设置局部变量@sum为0*/
mysql> CREATE TRIGGER test_tri BEFORE INSERT ON student1 FOR EACH ROW SET @sum=@sum+NEW.grade;
/*创建一个触发器名为test_tri,在studeng1中写入数据之前,设置新的@sum等于之前的@sum加上新的grade数据*/
mysql> INSERT INTO student1 VALUES(1,80.2),(2,90.3);
mysql> SELECT * FROM student1;
+------+-------+
| name | grade |
+------+-------+
| 1 | 80.20 |
| 2 | 90.30 |
+------+-------+
mysql> SELECT @sum; /*查看触发器触发后sum变量的值*/
+--------+
| @sum |
+--------+
| 170.50 |
+--------+
例:多表触发多条命令
多表触发器一旦动作超过一个时,需要使用begin和end进行开始和结束,且一旦符合触发的条件时,每个数据都会触发一次触发器,触发器会执行相对应的操作,每次触发触发器时,触发器中设定好的内容将都会别执行一遍。
mysql> CREATE TABLE test1(a1 INT);
mysql> CREATE TABLE test2(a2 INT);
mysql> CREATE TABLE test3(a3 INT);
mysql> CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
mysql> CREATE TABLE test4(a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,b4 INT DEFAULT 0);
/*以上均为创建的测试表*/
mysql> INSERT INTO test3 VALUES(NULL),(NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);
mysql> INSERT INTO test4(a4) VALUES(0),(0) ,(0) ,(0) ,(0),(0) ,(0) ,(0) ,(0) ,(0);
mysql> SELECT * FROM test3; /*因为test3有自增约束*/
+----+
| a3 |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----+
mysql> SELECT * FROM test4; /*b4默认值为0,a4有自增约束*/
+----+------+
| a4 | b4 |
+----+------+
| 1 | 0 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 0 |
| 7 | 0 |
| 8 | 0 |
| 9 | 0 |
| 10 | 0 |
+----+------+
mysql> DELIMITER //
mysql> CREATE TRIGGER test_tri1 BEFORE INSERT ON test1 /*在test1表中写入数据时触发以下内容*/
-> FOR EACH ROW BEGIN
-> INSERT INTO test2 SET a2=NEW.a1;
/*在test2中写入数据,设置a2字段的值=新写入的test1的a1字段,意思就是test1的值和test2的值一模一样*/
-> DELETE FROM test3 WHERE a3=NEW.a1;
/*删除test3中的条目,当a3的值=新写入的a1字段的值时*/
-> UPDATE test4 SET b4=b4+1 WHERE a4=NEW.a1;
/*当test4中a4的值=新写入的a1字段的值时,更新test4中的b4字段为b4的值+1*/
-> END //
mysql> INSERT INTO test1 VALUES(1),(6),(6),(1),(2),(3),(9);
/*写入了七条数据,执行了7次触发器*/
mysql> SELECT * FROM test1;
+------+
| a1 |
+------+
| 1 |
| 6 |
| 6 |
| 1 |
| 2 |
| 3 |
| 9 |
+------+
mysql> SELECT * FROM test2;
+------+
| a2 |
+------+
| 1 |
| 6 |
| 6 |
| 1 |
| 2 |
| 3 |
| 9 |
+------+ /*test1和test2数据一样*/
mysql> SELECT * FROM test3;
/*对于test3的触发器是删除数据,当test3中的a3字段中的值=test1中的a1数据时,将该条数据删除*/
/*所以对照test1中的数据为12369五条数据,test3中的12369也被删除*/
+----+
| a3 |
+----+
| 4 |
| 5 |
| 7 |
| 8 |
| 10 |
+----+
mysql> SELECT * FROM test4;
/*对于test4的触发器是b4=b4+1,当a4的值=a1的值时,b4默认值是0,a1的1661239中1和6都是两个*/
/*所以会加2次,其他各1次*/
+----+------+
| a4 | b4 |
+----+------+
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 0 |
| 6 | 2 |
| 7 | 0 |
| 8 | 0 |
| 9 | 1 |
| 10 | 0 |
+----+------+
查看触发器的语法:
SHOW TRIGGERS\G
删除触发器:
DROP TRIGGER 触发器名
触发器的删除和创建不会影响表中的数据
例:使用AFTER
mysql> CREATE TABLE myevent(id INT PRIMARY KEY AUTO_INCREMENT,
-> event1 VARCHAR(30)); /*创建一个表*/
mysql> CREATE TRIGGER test_tri2 AFTER UPDATE ON student1 FOR EACH ROW
-> INSERT INTO myevent VALUES(NULL,'after update');
/*在更新student1表之后,在表myevent中插入数据(NULL,'after update')*/
mysql> UPDATE student1 SET grade=80; /*更新student1表中的所有grade为80*/
mysql> SELECT * FROM myevent; /*查看表myevent,差入了两条数据,说明执行了两次触发器*/
+----+--------------+
| id | event1 |
+----+--------------+
| 1 | after update |
| 2 | after update |
+----+--------------+