Mysql触发器

Mysql的触发器和存储过程一样，都是嵌入到mysql的一段程序。触发器是mysql5新增的功能，目前线上凤巢系统、北斗系统以及哥伦布系统使用的数据库均是mysql5.0.45版本，很多程序比如fc-star管理端，sfrd(das)，dorado都会用到触发器程序，实现对于数据库增、删、改引起事件的关联操作。本文介绍了触发器的类型和基本使用方法，讲述了触发器使用中容易产生的误区，从mysql源码中得到触发器执行顺序的结论，本文最后是实战遭遇的触发器经典案例。没有特殊说明时，本文的实验均基于mysql5.0.45版本。


为什么要用触发器
我们在MySQL 5.0中包含对触发器的支持是由于以下原因:
MySQL早期版本的用户长期有需要触发器的要求。
我们曾经许诺支持所有ANSI标准的特性。
您可以使用它来检查或预防坏的数据进入数据库。
您可以改变或者取消INSERT, UPDATE以及DELETE语句。
您可以在一个会话中监视数据改变的动作。


语法
CREATE TRIGGER <触发器名称> <--
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名称>
FOR EACH ROW
<触发器SQL语句>

1. 语法：命名规则
触发器必须有名字，最多64个字符，可能后面会附有分隔符。它和MySQL中其他对象的命名方式基本相同。

2. 语法：触发时间{ BEFORE | AFTER }
触发器有执行的时间设置：可以设置为事件发生前或后。

3. 语法：事件{ INSERT | UPDATE | DELETE }
同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发。

4.语法：表
触发器是属于某一个表的:当在这个表上执行插入、更新或删除操作的时候就导致触发器的激活。我们不能给同一张表的同一个事件安排两个触发器。

5. 语法：(步长)触发间隔
触发器的执行间隔：FOR EACH ROW子句通知触发器每隔一行执行一次动作，而不是对整个表执行一次。

6. 语法：SQL语句
触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句，包括复合语句，但是这里的语句受的限制和函数的一样。

7.Privileges权限
你必须拥有相当大的权限才能创建触发器（CREATE TRIGGER）。
如果你已经是Root用户，那么就足够了。这跟SQL的标准有所不同。

因此在下一个版本的MySQL中，你完全有可能看到有一种叫做CREATE TRIGGER的新权限。
然后通过这样的方法赋予：
GRANT CREATE TRIGGER ON <表名称> TO <用户或用户列表>;

也可以通过这样收回权限：
REVOKE CREATE TRIGGER ON <表名称> FROM <用户或用户列表>;


创建触发器语法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

其中trigger_name标识触发器名称，用户自行指定；

trigger_time标识触发时机，用before和after替换；

trigger_event标识触发事件，用insert，update和delete替换；

tbl_name标识建立触发器的表名，即在哪张表上建立触发器；

trigger_stmt是触发器程序体；触发器程序可以使用begin和end作为开始和结束，中间包含多条语句；

下面给出sfrd一个触发器实例：

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER trig_useracct_update
AFTER UPDATE
ON SF_User.useracct FOR EACH ROW
BEGIN
IF OLD.ulevelid = 10101 OR OLD.ulevelid = 10104 THEN
IF NEW.ulevelid = 10101 OR NEW.ulevelid = 10104 THEN
if NEW.ustatid != OLD.ustatid OR NEW.exbudget != OLD.exbudget THEN
INSERT into FC_Output.fcevent set type = 2, tabid = 1, level = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget;
end if;
ELSE
INSERT into FC_Output.fcevent set type = 1, tabid = 1, level = 1, userid = NEW.userid, ustatid = NEW.ustatid, exbudget = NEW.exbudget;
END IF;
END IF;
END;

上述触发器实例使用了OLD关键字和NEW关键字。OLD和NEW可以引用触发器所在表的某一列，在上述实例中，OLD.ulevelid表示表 SF_User.useracct修改之前ulevelid列的值，NEW.ulevelid表示表SF_User.useracct修改之后 ulevelid列的值。另外，如果是insert型触发器，NEW.ulevelid也表示表SF_User.useracct新增行的 ulevelid列值；如果是delete型触发器OLD.ulevelid也表示表SF_User.useracct删除行的ulevelid列原值。

另外，OLD列是只读的，NEW列则可以在触发器程序中再次赋值。

上述实例也使用了IF，THEN ，ELSE，END IF等关键字。在触发器程序体中，在beigin和end之间，可以使用顺序，判断，循环等语句，实现一般程序需要的逻辑功能。


关于旧的和新创建的列的标识
在触发器的SQL语句中，你可以关联表中的任意列。但你不能仅使用列的名称去标识，那会使系统混淆，因为那里可能会有列的新名（这可能正是你要修改的，你的动作可能正是要修改列名），还有列的旧名存在。因此你必须用这样的语法来标识： "NEW . column_name"或者"OLD . column_name".这样在技术上处理（NEW | OLD . column_name）新和旧的列名属于创建了过渡变量（"transition variables"）。
对于INSERT语句,只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以在和NEW以及OLD同时使用。

下面是一个UPDATE中同时使用NEW和OLD的例子。
CREATE TRIGGER t21_au
BEFORE UPDATE ON t22
FOR EACH ROW
BEGIN
SET @old = OLD.s1;
SET @new = NEW.s1;
END;//

现在如果t21表中的s1列的值是55，那么执行了"UPDATE t21 SET s1 = s1 + 1"之后@old的值会变成55，而@new的值将会变成56。


CREATE和INSERT的例子
创建有触发器的表
这里所有的例程中我都假定大家的分隔符已经设置成//（DELIMITER //）。
CREATE TABLE t22 (s1 INTEGER)//

CREATE TRIGGER t22_bi
BEFORE INSERT ON t22
FOR EACH ROW
BEGIN
SET @x = 'Trigger was activated!';
SET NEW.s1 = 55;
END;//

在最开始我创建了一个名字为t22的表，然后在表t22上创建了一个触发器t22_bi，当我们要向表中的行插入时，触发器就会被激活，执行将s1列的值改为55的动作。

使用触发器执行插入动作
INSERT INTO t22 VALUES (1)//
让我们看如果向表t22中插入一行数据触发器对应的表会怎么样？ 这里的插入的动作是很常见的，我们不需要触发器的权限来执行它。甚至不需要知道是否有触发器关联。

SELECT @x, t22.* FROM t22//

大家可以看到INSERT动作之后的结果，和我们预期的一样，x标记被改动了，同时这里插入的数据不是我们开始输入的插入数据，而是触发器自己的数据。


"check"完整性约束例子
什么是"check"约束
在标准的SQL语言中，我们可以在（CREATE TABLE）创建表的过程中使用"CHECK (condition)"，
例如：
CREATE TABLE t25
(s1 INT, s2 CHAR(5), PRIMARY KEY (s1),
CHECK (LEFT(s2,1)='A'))
ENGINE=INNODB;

这里CHECK的意思是"当s2列的最左边的字符不是'A'时，insert和update语句都会非法"，MySQL的视图不支持CHECK，我个人是很希望它能支持的。但如果你很需要在表中使用这样的功能，我建议大家使用触发器来实现。


CREATE TABLE t25
(s1 INT, s2 CHAR(5),
PRIMARY KEY (s1))
ENGINE=INNODB//

CREATE TRIGGER t25_bi
BEFORE INSERT ON t25
FOR EACH ROW
IF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//

CREATE TRIGGER t25_bu
BEFORE UPDATE ON t25
FOR EACH ROW
IF LEFT(NEW.s2,1)<>'A' THEN SET NEW.s1=0; END IF;//

我只需要使用BEFORE INSERT和BEFORE UPDATE语句就行了，删除了触发器不会对表有影响，同时AFTER的触发器也不能修改NEW的过程变量（transition variables）。

为了激活触发器，我执行了向表中的行插入s1＝0的数据，之后只要执行符合LEFT(s2,1) <> 'A'条件的动作都会失败：

INSERT INTO t25 VALUES (0,'a') /* priming the pump */ //
INSERT INTO t25 VALUES (5,'b') /* gets error '23000' */ //


该抛弃旧的MySQL的手册了
我在这里警告大家不要相信过去的MySQL手册中所说的了。我们已经去掉了关于触发器的错误的语句，但是仍旧有很多旧版本的手册在网上，有的手册上说触发器就是存储过程，忘掉吧，你也已经看见了，触发器就是触发器，而存储过程还是存储过程。手册上还说触发器可以从其他表上来删除，或者是当你删除一个事务的时候激发，无论他说的是什么意思，忘掉吧，MySQL不会去实现这些的。最后关于说使用触发器会对查询速度产生影响的说法也是错的，触发器不会对查询产生任何影响。