MYSQL触发器的使用

一、什么是触发器

　　触发器（TRIGGER）是 MySQL 的数据库对象之一，从5.0.2版本开始支持。该对象与编程语言中的函数非常类似，都需要声明、执行等。但是触发器的执行不是由程序调用，也不是由手工启动，而是由事件来触发、激活从而实现执行。有点类似 DOM 中的事件。

　　触发器是由事件触发，事件包括增，删，改操作，当数据库执行这些操作时，会激活触发器执行相应的操作。

二、触发器的语法

　　1、创建触发器

CREATE TRIGGER trigger_name trigger_time trigger_event 
ON tb_name FOR EACH ROW trigger_stmt

trigger_name：触发器名称
tirgger_time：触发执行时间

BEFORE：事件之前触发
AFTER：事件之后触发

trigger_event：触发事件

INSERT：插入某一行时激活触发器，INSERT，LOAD DATA，REPLACE 语句可以触发
UPDATE：更改某一行时激活触发器，UPDATE 语句可以触发
DELETE：删除某一行时激活触发器，DELETE，REPLACE 语句可以触发

tb_name：触发器要执行的哪张表
FOR EACH ROW：触发频率为每一行触发一次
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用 BEGIN 和 END 包含的多条语句

　　　2、创建只有一个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句;

　　　3、创建有多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
    执行语句列表
END;

　　　　MySQL 默认是以分号（;）作为结束执行符号，与触发器执行语句列表中需要的分行起冲突，为解决此问题可用DELIMITER，如DELIMITER ;; ，就是将结束符号变成 ;;。

DELIMITER ;;
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
    执行语句列表
END ;;
DELIMITER;

　　　　4、删除触发器

DROP TRIGGER IF EXISTS tb_name;
示例

三、触发器示例演示

　　1、准备数据

-- 创建user表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `account` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 创建对user表操作历史表
DROP TABLE IF EXISTS `user_history`;
CREATE TABLE `user_history` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `operatetype` varchar(200) NOT NULL,
  `operatetime` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

　　2、创建 user 表插入事件对应的触发器

DROP TRIGGER IF EXISTS `tri_insert_user`;
DELIMITER ;;
CREATE TRIGGER `tri_insert_user` AFTER INSERT ON `user` FOR EACH ROW begin
    INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (new.id, 'add a user',  now());
end
;;
DELIMITER ;

　　3、创建 user 表删除事件对应的触发器

DROP TRIGGER IF EXISTS `tri_delete_user`;
DELIMITER ;;
CREATE TRIGGER `tri_delete_user` AFTER DELETE ON `user` FOR EACH ROW begin
    INSERT INTO user_history(user_id, operatetype, operatetime) VALUES (old.id, 'delete a user', now());
end
;;
DELIMITER ;

　　　4、创建 user 表更新事件对应的触发器

DROP TRIGGER IF EXISTS `tri_update_user`;
DELIMITER ;;
CREATE TRIGGER `tri_update_user` AFTER UPDATE ON `user` FOR EACH ROW begin
    INSERT INTO user_history(user_id,operatetype, operatetime) VALUES (new.id, 'update a user', now());
end
;;
DELIMITER ;

至此，全部表及触发器创建完成，开始验证结果，分别做插入、修改、删除事件，执行以下语句，观察 user_history是否自动产生操作记录。

INSERT INTO user(account, name, address) VALUES ('user1', 'user1', 'user1');
INSERT INTO user(account, name, address) VALUES ('user2', 'user2', 'user2');
UPDATE user SET name = 'user3', account = 'user3', address='user3' where name='user1';
DELETE FROM `user` where name = 'user2';

触发器总结

触发器对性能有损耗，应当非常慎重使用。
对于事务表，触发器执行失败则整个语句回滚。
Row 格式主从复制，触发器不会在从库上执行。

因为从库复制的肯定是主库已经提交的数据，既然已经提交了说明触发器已经被触发过了，所以从库不会执行。

使用触发器时应防止递归执行。

delimiter //
create trigger trg_test
    before update on 'test_trigger'
    for each row
begin
    update test_trigger set score=20 where name = old.name;  -- 又触发了update操作，循环触发了
end;//

参考：

https://www.cnblogs.com/yinjw/p/11767649.html