触发器

概念

属于一个特殊的存储过程,属于嵌入到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 |
+----+--------------+

评论




正在载入...
PoweredHexo
HostedAliyun
DNSAliyun
ThemeVolantis
UV
PV
BY-NC-SA 4.0