MySQL之触发器

• 触发器介绍

　　用途：当我们希望用户在使用数据库时（增删改查），数据库会自动执行一些命令，而不是人为的去敲命令，或者是开发人员在代码里写命令。

　　场景：我们希望用户在对表进行修改时，可以把修改同步到另一张表

　　实现：假设现有一张表stu1，为了方便，我们创建另一张表stu2，然后让stu2的内容始终与stu1的保持同步

　　创建stu2的SQL：CREATE TABLE stu2 LIKE stu1;

• 创建触发器

　　1. 创建触发器的语法

mysql>CREATE TRIGGER trigger_name
    ->trigger_time
    ->trigger_event ON tb_name
    ->FOR EACH ROW
    ->trigger_stmt；

　　解释：

　　trigger_name：触发器的名称；

　　trigger_time：触发时机，取值为BEFORE 或者AFTER；

　　trigger_event：触发时间，取值为INSERT, UPDATE或者DELETE；

　　tb_name：建立触发器的表名；

　　FOR EACH ROW：表示任何一条记录上的操作满足触发事件都会触发该触发器，也就是说触发器的触发频率是针对每一行数据触发一次。

　　trigger_stmt：触发器事件，可以是一条SQL语句，也可以是BEGIN和END包含的多条语句；

　　2. 创建INSERT型触发器

　　例子：创建触发器，用于向测试表stu1中添加记录后自动将记录备份到stu2中。

　　先将默认的结尾符号；改为//

mysql>DELIMITER //

mysql>CREATE TRIGGER afterinsert_on_stu1
    ->AFTER
    ->INSERT ON stu1
    ->FOR EACH ROW
    ->BEGIN
    ->INSERT INTO stu2 (id, name) VALUES (NEW.id, NEW.name);
    ->END //

mysql>DELIMITER ；    

 

　　注：DELIMITER // ：因为数据库中默认是以分号";"当做结尾的，但是因为触发器中也会包含分号，为了让数据库区分，所以这儿使用DELIMITER命令来修改数据库中结尾的符号为//，使用完后，也可以使用命令DELIMITER ；改回来，（命令和分号之间是有空格的）

　　3. 创建DELETE型触发器

mysql>CREATE TRIGGER afterdelete_on_stu1
       ->AFTER
       ->DELETE ON stu1
       ->FOR EACH ROW
       ->BEGIN 
       ->DELETE FROM stu2 WHERE id=OLD.id;
       ->END //

注：mysql中定义了NEW和OLD来表示触发器所在表中触发了触发器的哪一行

• 在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据
• 在UPDATE型触发器中，OLD用来表示将要或者已经被修改的原数据，NEW用来表示将要或已经被修改的新数据
• 在DELETE型触发器中，OLD用来表示将要或已经被修改的原数据

NEW关键字的用法：NEW.columnName，OLD用法类似

OLD是只读的，而NEW可以在触发器中使用SET赋值，这样就不会再次触发触发器了。

 

•  查看触发器

　　1. 使用SHOW TRIGGERS查看

　　这是我之前在其他表中创建的INSERT和DELETE触发器

​

　　2. 在information_schema.triggers表中查看触发器

　　information_schema是mysql数据库默认就创建的一个数据库，triggers是该数据库下的一张表

mysql>SELECT * FROM information_schema.triggers
    ->WHERE trigger_name='afterinsert_on_stu1'\G

​

 

• 使用触发器

　　执行顺序：触发器的执行顺序依次是BEFORE触发器、行操作、AFTER，过程中出错的话，将不会执行后面的操作，事务表出错可以回滚，非事务表不能回滚。

 

• 删除触发器

　　语法：DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

　IF EXISTS是可选参数，加了这个参数，如果要删除的触发器不存在的话，那么不会报错，而是返回一个警告

　　例如：删除数据表stu1中的DELETE触发器

mysql>DROP TRIGGER stu1.afterdelete_on_stu1;

　　下面是删除某个不存在的触发器时，使用IF EXISTS和不使用的区别

​