MySQL数据库触发器

1.触发器概念
MySQL触发器和存储过程一样，都是嵌入到MySQL的一段程序。触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATE、DELETE。如果定义了触发器，当数据库执行这些语句的时候就会激活触发器执行相应的操作，触发程序是与表有关的命令数据库对象，当表上出现特定事件，将激活该对象。
　　触发器是一个特殊的存储过程，不同的是，执行存储过程要使用call语句来调用，而触发器的执行不需要用call来调用，也不需要手工启动，只要当一个预定义的事件发生，触发器就会被MySQL自动调用。触发器可以查询其他表，而且可以包含复杂的SQL语句。

2.触发器语法
(1)创建只有一条执行语句的触发器

　　语法结构如下：https://www.cndba.cn/hbhe0316/article/22218

CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW trigger_stmt

　　trigger_name：用户自定义的触发器名称；
　　trigger_time：标识触发事件，可以指定为before(时间发生前执行)或after(事件发生后执行)；
　　trigger_event：标识触发事件，包括INSERT、UPDATE、DELETE；
　　table_name：触发器建立在哪个表上；
　　trigger_stmt：触发器执行语句。

(2)创建有多个执行语句的触发器
语法结构如下：

CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW BEGIN 语句执行列表 END

3.当触发器有至少一条的执行语句时，多条执行语句需要用BEGIN和END包裹，分别表示整个代码块的开始和结束。
为演示触发器操作，我们先创建一下三个数据表：

mysql> use testdb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table tb_student( id int PRIMARY key auto_increment, name varchar(10) );
Query OK, 0 rows affected (0.01 sec)

mysql> create table tb_before_trigger( id int PRIMARY key auto_increment, num int , time_now datetime NULL DEFAULT CURRENT_TIMESTAMP );
Query OK, 0 rows affected (0.03 sec)

mysql> create table tb_after_trigger( id int PRIMARY key auto_increment, num int , time_now datetime NULL DEFAULT CURRENT_TIMESTAMP );
Query OK, 0 rows affected (0.02 sec)

示例1：创建一个名为before_trigger的触发器，该触发器会在每次对表tb_student执行insert操作前触发，触发时会往before_trigger表插入一条包含tb_student表总记录数的记录。https://www.cndba.cn/hbhe0316/article/22218

delimiter // 
create trigger before_trigger 
before insert on tb_student 
for each row 
begin 
    insert into tb_before_trigger (num) select count(*) from tb_student; 
end // 
delimiter ;

　示例2：创建一个名为after_trigger的触发器，该触发器会在每次对表tb_student执行insert操作前触发，触发时会向before_trigger表插入一条包含tb_student表总记录数的记录。

delimiter // 
create trigger after_trigger 
after insert on tb_student 
for each row 
begin 
    insert into tb_after_trigger (num) select count(*) from tb_student; 
end // 
delimiter ;

4.查看triggers是否创建成功

mysql> show triggers;
+----------------+--------+------------+----------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| Trigger        | Event  | Table      | Statement                                                                        | Timing | Created                | sql_mode                                                                                                                                  | Definer        | character_set_client | collation_connection | Database Collation |
+----------------+--------+------------+----------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
| before_trigger | INSERT | tb_student | begin 
insert into tb_before_trigger (num) select count(*) from tb_student; 
end | BEFORE | 2021-11-25 10:19:32.95 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
| after_trigger  | INSERT | tb_student | begin 
insert into tb_after_trigger (num) select count(*) from tb_student; 
end  | AFTER  | 2021-11-25 10:20:41.21 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@localhost | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
+----------------+--------+------------+----------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+----------------+----------------------+----------------------+--------------------+
2 rows in set (0.01 sec)

5.查看trigger是否可以正常工作
　来测试一下示例1和示例2中创建的触发器，往tb_student表中插入一条数据(插入前3个表没有任何记录)：

mysql>  insert into tb_student (name) values('lisi');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_student;
+----+----------+
| id | name     |
+----+----------+
|  1 | lisi     |
+----+----------+
1 rows in set (0.00 sec)

mysql> select * from tb_before_trigger;
+----+------+---------------------+
| id | num  | time_now            |
+----+------+---------------------+
|  1 |    0 | 2021-11-25 10:21:24 |
+----+------+---------------------+
1 row in set (0.01 sec)

mysql> select * from tb_after_trigger;
+----+------+---------------------+
| id | num  | time_now            |
+----+------+---------------------+
|  1 |    1 | 2021-11-25 10:21:24 |
+----+------+---------------------+
1 row in set (0.00 sec)

版权声明：本文为博主原创文章，未经博主允许不得转载。

MYSQL