10-数据库触发器

1. Trigger 简介

【概念】
它是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。
这种特性可以协助应用在数据库端确保数据的完整性。

【基本语法】

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、DELETE、UPDATE
# @tb_name：表示建立触发器的表名，就是在哪张表上建立触发器
# @trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用 BEGIN 和 END 包含的多条语句

所以 MySQL 可以创建以下 6 种触发器：
BEFORE INSERT, BEFORE DELETE, BEFORE UPDATE
AFTER INSERT, AFTER DELETE, AFTER UPDATE

【包含多个执行语句语法】

DELIMITER $
CREATE TRIGGER 触发器名 触发时机 触发事件 ON 表名 FOR EACH ROW
BEGIN
    执行语句列表;
    ...;
END $
DELIMITER ;

SQL 语句分隔符问题：
一般情况下，mysql默认是以 ; 作为结束执行语句，与触发器中需要的分行有冲突。
为解决此问题可用 DELIMITER 关键字，如：DELIMITER #，可以将结束符号变成 #
当触发器创建完成后，可以用 DELIMITER ; 来将结束符号变成 ;

【Trigger CRUD】

# 增 Create
CREATE TRIGGER 触发器名 触发时机 触发事件 ON 表名 FOR EACH ROW 触发器程序体
# 查 Retrieve
SHOW TRIGGERS
# 改 Update
ALTER TRIGGER 触发器名 触发时机 触发事件 ON 表名 FOR EACH ROW 触发器程序体
# 删 Delete
DROP TRIGGER trigger_name

【NEW 和 OLD】

• NEW 对象：SQL语句执行之前的记录对象
• OLD 对象：SQL语句执行之后的记录对象

【基本使用】

• 当需要在操作 user 表的时候做独立的数据库操作记录，并备份 user 表的数据 —— Demo1
• 当 user 表中发生了 DML 行为，那么就会修改关联表中的某个字段，比如数量 —— Demo2
• …

2. Trigger Demo1 For Log

# 创建 user 表：id 主键,自增；name 不允许空
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`(
  `id` INT PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(20) NOT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

# 插入测试数据
INSERT INTO `user`(NAME) VALUES('jack');
INSERT INTO `user`(NAME) VALUES('rose');
INSERT INTO `user`(NAME) VALUES('tom');
INSERT INTO `user`(NAME) VALUES('jerry');

SELECT `id`,`name` FROM `user`;


# 创建 user 表对应的备份表 user_log，记录 user 表中所有 DML 操作被触发器语句触发而记录的内容
DROP TABLE IF EXISTS `user_log`;
CREATE TABLE `user_log` (
  `id` INT(10) NOT NULL AUTO_INCREMENT,
  `create_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, # 该字段数据有变化的时候自动更新该时间
  `current_user` VARCHAR(20) DEFAULT NULL,
  `operation` VARCHAR(64) DEFAULT NULL,
  `name` VARCHAR(64) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

SELECT * FROM `user_log`;


# -- 【创建 INSERT 触发器】 -- #
DROP TRIGGER IF EXISTS `user_log_insert`;
DELIMITER $  # 设置 sql 语句的结束符为 $
CREATE TRIGGER user_log_insert AFTER INSERT ON `user` FOR EACH ROW
BEGIN
  DECLARE s1 VARCHAR(64) CHARACTER SET utf8; # 语句①：定义变量 s1，后面发现中文字符编码出现乱码时设置字符集
  SET s1 = "新增";  # 语句②：为 s1 赋值
  INSERT INTO user_log(`current_user`, `operation`, `name`) VALUES(USER(), s1, NEW.name); # 语句③：NEW 关键字，代表即将插入 user 表的数据
END $  # 此时 CREATE 语句到此为一整句的结束
DELIMITER ;  # 恢复 sql 语句的结束符为默认的分号 ;

# 显示所有触发器
SHOW TRIGGERS;

# 插入一条数据，并查询备份表，测试触发器是否产生记录
INSERT INTO `user` (NAME) VALUES ('周杰伦');
SELECT * FROM `user`;
SELECT * FROM `user_log`;


# -- 【创建 DELETE 触发器】 -- #
DROP TRIGGER IF EXISTS `user_log_delete`;
DELIMITER $
CREATE TRIGGER user_log_delete AFTER DELETE ON `user` FOR EACH ROW
BEGIN
  DECLARE s1 VARCHAR(64) CHARACTER SET utf8; # 语句①：定义变量 s1，后面发现中文字符编码出现乱码时设置字符集
  SET s1 = "删除";  # 语句②：为 s1 赋值
  INSERT INTO user_log(`current_user`, `operation`, `name`) VALUES(USER(), s1, OLD.name); # 语句③：OLD 关键字，代表未删除之前 user 表的数据
END $
DELIMITER ;

# 显示所有触发器
SHOW TRIGGERS;

# 删除一条数据，并查询备份表，测试触发器是否产生记录
DELETE FROM `user` WHERE `name`='周杰伦';
SELECT * FROM `user`;
SELECT * FROM `user_log`;


# -- 【创建 UPDATE 触发器】 -- #
DROP TRIGGER IF EXISTS `user_log_update`;
DELIMITER $
CREATE TRIGGER user_log_update AFTER UPDATE ON `user` FOR EACH ROW
BEGIN
  DECLARE s1 VARCHAR(64) CHARACTER SET utf8;
  SET s1 = "更新";
  INSERT INTO user_log(`current_user`, `operation`, `name`) VALUES(USER(), s1, CONCAT(OLD.name, ' -> ', NEW.name));
END $
DELIMITER ;

# 显示所有触发器
SHOW TRIGGERS;

# 更新一条数据，并查询备份表，测试触发器是否产生记录
UPDATE `user` SET NAME='jay' WHERE id=1;
SELECT * FROM `user`;
SELECT * FROM `user_log`;

3. Trigger Demo2 For Data

# 创建班级表
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class`(
    `cid` INT PRIMARY KEY AUTO_INCREMENT,
    `cname` VARCHAR(30) DEFAULT NULL,
    `num` INT DEFAULT 0
) ENGINE=INNODB DEFAULT CHARSET=utf8;
    
INSERT INTO class VALUES(1,"小班",0);
INSERT INTO class VALUES(2,"中班",0);
INSERT INTO class VALUES(3,"大班",0);

SELECT * FROM class;

# 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
    `sid` INT PRIMARY KEY AUTO_INCREMENT,
    `sname` VARCHAR(30) DEFAULT NULL,
    `age` INT DEFAULT 0,
    `cno` INT DEFAULT 0
) ENGINE=INNODB DEFAULT CHARSET=utf8;

SELECT * FROM student;

# 学生表添加一条学生记录, 班级表字段 num 加 1
DROP TRIGGER IF EXISTS `trigger_add`;
DELIMITER $
CREATE TRIGGER trigger_add AFTER INSERT ON student FOR EACH ROW
BEGIN
  UPDATE class SET num = num + 1 WHERE cid = NEW.cno;
END $
DELIMITER ;

SHOW TRIGGERS;

INSERT INTO student VALUES(1, "小三", 3, 1);
SELECT * FROM class;
SELECT * FROM student;
INSERT INTO student VALUES(2, "小四", 4, 1);
INSERT INTO student VALUES(3, "小五", 5, 1);


# 学生表删除一条学生记录, 班级表字段 num 减1
DROP TRIGGER IF EXISTS `trigger_sub`;
DELIMITER $
CREATE TRIGGER trigger_sub AFTER DELETE ON student FOR EACH ROW
BEGIN
  UPDATE class SET num = num - 1 WHERE cid = OLD.cno;
END $
DELIMITER ;

SHOW TRIGGERS;

DELETE FROM student WHERE sname = "小三";
SELECT * FROM class;
SELECT * FROM student;

4. Trigger 总结

总结：
触发器是基于行触发的，所以删除、新增或者修改操作可能都会激活触发器，所以不要编写过于复杂的触发器，也不要增加过得的触发器，这样会对数据的插入、修改或者删除带来比较严重的影响，同时也会带来可移植性差的后果，所以在设计触发器的时候一定要有所考虑。

触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

数据库触发器有以下的作用：

1. 安全性。可以基于数据库的值使用户具有操作数据库的某种权利。

可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。

可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%。

2. 审计。可以跟踪用户对数据库的操作。

审计用户操作数据库的语句。

把用户对数据库的更新写入审计表。

3. 实现复杂的数据完整性规则

实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。

提供可变的缺省值。

4. 实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。例如，在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。

在修改或删除时级联修改或删除其它表中的与之匹配的行。

在修改或删除时把其它表中的与之匹配的行设成NULL值。

在修改或删除时把其它表中的与之匹配的行级联设成缺省值。

触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。例如，可以在books.author_code 列上生成一个插入触发器，如果新值与auths.author_code列中的某值不匹配时，插入被回退。

5. 同步实时地复制表中的数据。

6. 自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。