MySQL 触发器

MySQL是最受欢迎的开源RDBMS，被社区和企业广泛使用。触发器是MySQL在5.0.1中 增加的三大新功能之一，另外两个师兄弟是视图（view）与存储过程（procedure）。均属于相对“高级”一点的数据库必须功能。

一、什么是触发器

触发器（trigger）：监视某种情况，并触发执行某种操作。触发器是在表中数据发生更改时自动触发执行的，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（insert，delete， update）时就会激活它执行。也就是说触发器只执行DML事件(insert、update和delete)

MySQL触发器的作用

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

• 可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。
• 可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%。

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

• 审计用户操作数据库的语句。
• 把用户对数据库的更新写入审计表。

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

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

4. 实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。

例如，在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。

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

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

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

二、触发器语法

1、创建语法四要素

1. 监视地点（table）
2. 监视事件（insert、update、delete）
3. 触发时间（after、before）
4. 触发事件（insert、update、delete）

2、语法公式

CREATE TRIGGER <trigger_name>  
  
  BEFORE|AFTER
 
  INSERT|UPDATE|DELETE  ON <table_name> # 表名
  
  FOR EACH ROW  # 这句话在mysql是固定的
 
  BEGIN
 
  <触发的SQL语句>（调用NEW/OLD参数）;
 
  END

3、语法参数说明

1. CREATE TRIGGER <trigger_name> --- 触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.

2. { BEFORE | AFTER } --- 触发器触发时间设置：可以设置为事件发生前或后（前：一般用于校验；后：一般用于关联）。

3. { INSERT | UPDATE | DELETE } -- 设定触发事件：如执行insert、update或delete的过程时激活触发器。

4. ON <table_name> --- 触发器是属于某一个表的: 当在这个表上执行 INSERT|UPDATE|DELETE 操作的时候就导致触发器的激活. 同时，我们不能给同一张表的“同一个事件”安排两个触发器（意味着不能同时有两个Insert触发器）。

5. FOR EACH ROW --- 触发器的执行间隔（必有的公式内容）：FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。

6. <触发的SQL语句> --- 触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句， 包括复合语句，但是这里的语句受的限制和函数的一样。当然，触发SQL中可以调用“触发了（ INSERT | UPDATE | DELETE ）触发器的那一行数据”。

例如：

create trigger add_stu
after insert on student for each row 
begin
      insert into student_score ( stu_id, score, rank)
        values( NEW.stuid, NEW.username);  -- NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据
end;

4、NEW 与 OLD关键字详解

MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体地：

① 在INSERT型触发器中，NEW用来表示将要（BEFORE）或已经（AFTER）插入的新数据；
② 在UPDATE型触发器中，OLD用来表示将要或已经被修改的原数据，NEW用来表示将要或已经修改为的新数据；
③ 在DELETE型触发器中，OLD用来表示将要或已经被删除的原数据；

另外，原则上请编写简单高效的触发执行语句，以免悄无声息的浪费过多资源你还不知道！

例如：

CREATE TRIGGER sys_operation_log_trigger BEFORE INSERT ON sys_operation_log FOR EACH ROW
BEGIN
	IF NEW.level = 'web' THEN
		SET NEW.level = '重要';
	END IF;
END