分类： Mysql/postgreSQL
2009-05-13 09:25:39
Why Triggers 为什么要用触发器
我们在MySQL 5.0中包含对触发器的支持是由于以下原因:
MySQL早期版本的用户长期有需要触发器的要求。
您可以使用它来检查或预防坏的数据进入数据库。
您可以改变或者取消INSERT, UPDATE以及DELETE语句。
您可以在一个会话中监视数据改变的动作。
复合语句(BEGIN / END)是合法的.
流控制（Flow-of-control）语句(IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE,ITERATE)也是合法的.
变量声明(DECLARE)以及指派(SET)是合法的.
允许条件声明.
异常处理声明也是允许的.
但是在这里要记住函数有受限条件:不能在函数中访问表.因此在函数中使用以下语句是非法的。
ALTER 'CACHE INDEX' CALL COMMIT CREATE DELETE
DROP 'FLUSH PRIVILEGES' GRANT INSERT KILL
LOCK OPTIMIZE REPAIR REPLACE REVOKE
ROLLBACK SAVEPOINT 'SELECT FROM table'
'SET system variable' 'SET TRANSACTION'
SHOW 'START TRANSACTION' TRUNCATE UPDATE
在触发器中也有完全一样的限制.
触发器相对而言比较新，因此会有（bugs）缺陷.所以我在这里给大家警告，就像我在存储过程书中所说那样.不要在含有重要数据的数据库中使用这个触发器，如果需要的话在一些以测试为目的的数据库上使用，同时在你对表创建触发器时确认这些数据库是默认的。
Syntax 语法
CREATE TRIGGER <触发器名称> <--
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名称>
FOR EACH ROW
<触发器SQL语句>
触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.
这里我有个习惯：就是用表的名字＋'_'＋触发器类型的缩写.因此如果是表t26，触发器是在事件UPDATE（参考下面的点（2）和（3））之前（BEFORE）的，那么它的名字就是t26_bu。
2. Syntax: Time 语法：触发时间
CREATE TRIGGER <触发器名称>
{ BEFORE | AFTER } <--
{ INSERT | UPDATE | DELETE }
ON <表名称>
FOR EACH ROW
<触发的SQL语句>
触发器有执行的时间设置：可以设置为事件发生前或后。
3. Syntax: Event语法：事件
CREATE TRIGGER <触发器名称>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE } <--
ON <表名称>
FOR EACH ROW
<触发的SQL语句>
同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发。
4. Syntax: Table 语法：表
CREATE TRIGGER <触发器名称>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名称> <--
FOR EACH ROW
<触发的SQL语句>
触发器是属于某一个表的:当在这个表上执行插入、更新或删除操作的时候就导致触发器的激活.
我们不能给同一张表的同一个事件安排两个触发器。
5. Syntax: Granularity 语法：（ ：（ 步长）触发间隔
CREATE TRIGGER <触发器名称>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名称>
FOR EACH ROW <--
<触发的SQL语句>
触发器的执行间隔：FOR EACH ROW子句通知触发器每隔一行执行一次动作，而不是对整个表执行一次。
6. Syntax: Statement 语法：语句
CREATE TRIGGER <触发器名称>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <表名称>
FOR EACH ROW
<触发的SQL语句> <--
触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句，包括复合语句，但是这里的语句受的限制和函数的一样。
Privileges权限
GRANT CREATE TRIGGER ON <表名称> TO <用户或用户列表>;
也可以通过这样收回权限：
REVOKE CREATE TRIGGER ON <表名称> FROM <用户或用户列表>;
值得注意的是mysql官方文档
：http://dev.mysql.com/doc/refman/5.1/en/privileges-provided.html#priv_super
说在mysql5.1.6之前的版本，要想创建数据库必须要有超级用户的权限，所以如果您使用的是5.1.6之前的版本，即使运行上述赋权命令也是不可以创建触发器的。
Referring to OLD and NEW columns 关于旧的和新创建的列的标识
在触发器的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。
Example of CREATE and INSERT CREATE和INSERT的例子
CREATE table with trigger创建有触发器的表
这里所有的例程中我都假定大家的分隔符已经设置成//（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 on table w ith a trigger使用触发器执行插入动作
mysql> INSERT INTO t22 VALUES (1)//
让我们看如果向表t2中插入一行数据触发器对应的表会怎么样？
这里的插入的动作是很常见的，我们不需要触发器的权限来执行它。甚至不需要知道是否有触发器关联。
mysql> SELECT @x, t22.* FROM t22//
+------------------------+------+
| @x | s1 |
+------------------------+------+
| Trigger was activated! | 55 |
+------------------------+------+
1 row in set (0.00 sec)
大家可以看到INSERT动作之后的结果，和我们预期的一样，x标记被改动了，同时这里插入的数据不是我们开始输入的插入数据，而是触发器自己的数据。
Example of a "check" constraint
"check"完整性约束例子
What's a "check" constraint 什么是"check"约束
例如：
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' */ //
Don't Believe The Old MySQL Manual
该抛弃旧的MySQL的手册了
我在这里警告大家不要相信过去的MySQL手册中所说的了。我们已经去掉了关于触发器的错误的语句，但是仍旧有很多旧版本的手册在网上，举个例子，这是一个德国的Url上的：
http://dev.mysql.com/doc/mysql/de/ANSI_diff_Triggers.html.
这个手册上说触发器就是存储过程，忘掉吧，你也已经看见了，触发器就是触发器，而存储过程还是存储过程。
手册上还说触发器可以从其他表上来删除，或者是当你删除一个事务的时候激发，无论他说的是什么意思，忘掉吧，MySQL不会去实现这些的。
最后关于说使用触发器会对查询速度产生影响的说法也是错的，触发器不会对查询产生任何影响。
Bugs
（不好的东西就不翻译了）
On December 14 2004, I did an "Advanced Search" in for 'trigger' or
'triggers', I found that there were 17 active bugs as of that date. Of course they might disappear
before you read this, but just in case they haven't, I'll mention the important ones. If they're still
there, you'll have to work around them when you're trying triggers.
Bug#5859 DROP TABLE does not drop triggers.
（删除表的时候没有自动删除触发器）
When you drop a table, dropping the table's triggers should be automatic.
Bug#5892 Triggers have the wrong namespace.
（触发器的命名空间有错，你必须在前面加上表的名字才能删除触发器，下面是例子）
You have to say "DROP TRIGGER .
The correct way is "DROP TRIGGER
MySQL触发器
一、 MySQL触发器概念
MySQL5.0以上版本支持触发器，触发器是与表有关的命名数据库对象，当表出现特定事件时，将激活该对象。
二、 MySQL触发器语法
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
trigger_time：触发动作时间，可以是BEFORE或AFTER，以指明触发程序是在激活它的语句之前或之后触发。BEFORE update能否取到new.字段值？BEFORE AFTER使用时的区别?
Before与After区别：before：(insert、update)可以对new进行修改，after不能对new进行修改。两者都不能修改old数据。
在本表插入字段值需要用new计算，只能用before，在别的表中记录插入成功记录或统计插入的条数用after。
trigger_event：激活触发程序的语句类型。
INSERT：将新行插入表时激活触发程序，如INSERT、LOAD DATA和REPLACE语句。
UPDATE：更改某一行时激活触发程序，如UPDATE语句。
DELETE：从表中删除某一行时激活触发程序，如DELETE和REPLACE语句
trigger_stmt：触发程序激活时执行的语句。如果你打算执行多个语句，可使用BEGIN ... END复合语句结构。
DELIMITER |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END
|
DELIMITER ;
注：
1、 使用别名OLD和NEW，能够引用与触发程序相关的表中的列。OLD.col_name在更新或删除它之前，引用已有行中的1列，是否可以引
用old与trigger_time无关，NEW.col_name在更新它之后引用将要插入的新行的1列或已有行的1列。
INSERT：只可引用NEW、DELETE：只可引用OLD、 UPDATE：可以引用NEW或OLD。
2、激活触发程序时，对于触发程序引用的所有OLD和NEW列，需要具有SELECT权限，对于作为SET赋值目标的所有NEW列, 需要具
有UPDATE权限。
3、CREATE TRIGGER语句需要SUPER权限。
4、show triggers \G :显示数据库中所有触发器。
5、对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。
6、触发程序不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL
7、触发程序不能使用以显式或隐式方式开始或结束事务的语句，如START TRANSACTION、COMMIT或ROLLBACK。
DROP TRIGGER [schema_name.]trigger_name
三、 MySQL触发器例子
可以在从表中删除每一行之前，或在更新了每一行后激活触发程序。
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account FOR EACH ROW SET @sum = @sum + NEW.amount;
SET @sum = 0;
INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
SELECT @sum AS 'Total amount inserted';
注：
如果触发程序比较复杂，可以将其单独定义在存储过程中，然后使用简单的CALL语句从触发程序调用存储程序
MySQL处理错误的方式如下：
如果BEFORE触发程序失败，不执行相应行上的操作。
仅当BEFORE触发程序（如果有的话）和行操作均已成功执行，才执行AFTER触发程序。
如果在BEFORE或AFTER触发程序的执行过程中出现错误，将导致调用触发程序的整个语句的失败。
四、 项目中使用
应用场景：记录会员表user_login、user_x表变更情况
DELIMITER $$
create trigger `member`.`tri_userloginmod` AFTER UPDATE on `member`.`user_login`
for each row BEGIN
SELECT COUNT(1) INTO @count FROM member_modify WHERE userid=OLD.userid;
IF @count<1 THEN
INSERT INTO member_modify(userid,modify_time) values(OLD.userid,NOW());
ELSE
UPDATE member_modify SET modify_time=NOW() WHERE userid=OLD.userid;
END IF ;
END;
$$
DELIMITER ;
for i in `seq 0 99`;do mysql -h10.34.43.111 member -p -e "DELIMITER $$
create trigger member.tri_user${i}mod AFTER UPDATE on member.user_${i}
for each row BEGIN
SELECT COUNT(1) INTO @count FROM member_modify WHERE userid=OLD.userid;
IF @count<1 THEN
INSERT INTO member_modify(userid,modify_time) values(OLD.userid,NOW());
ELSE
UPDATE member_modify SET modify_time=NOW() WHERE userid=OLD.userid;
END IF ;
END;
$$
DELIMITER ;";done
