MySQL触发器

1. 创建触发器

CREATE
    [DEFINER = { user | CURRENT_USER }]
    TRIGGER trigger_name
    trigger_time trigger_event
    ON tbl_name FOR EACH ROW
    trigger_body

trigger_time: { BEFORE | AFTER }

trigger_event: { INSERT | UPDATE | DELETE }

触发器是与表相关的数据库对象，当表上发生特定事件时，将激活该对象。触发器只能在永久性表上创建，不能在TEMPORARY表或视图上创建。

DEFINER子句是指定触发器激活时检查MySQL用户的访问权限，默认为CURRENT_USER，即执行创建触发器的用户

trigger_time是触发器的动作时间。它可以是BEFORE或AFTER，以指明触发器在行操作之前或之后触发。
trigger_event指明了激活触发器的语句的类型。可指定下列值：

• INSERT：将新行插入表时激活触发器，如通过INSERT、LOAD DATA和REPLACE语句。
• UPDATE：更改某一行时激活触发器，如通过UPDATE语句。
• DELETE：从表中删除某一行时激活触发器，如通过DELETE和REPLACE语句，DROP TABLE、TRUNCATE TABLE、DROP PARTITION不会执行触发器。

INSERT INTO .. ON DUPLICATE UPDATE语句将触发BEFORE INSERT和AFTER INSERT触发器，或者BEFORE UPDATE和AFTER UPDATE触发器，具体情况取决于影响行上是否有重复键。

trigger_stmt是当触发器激活时执行的语句。如果想执行多个语句，可使用BEGIN ... END复合语句结构或调用存储过程。

2. 删除触发器

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name   --删除表时也会删除表中的触发器

3. 查看触发器

mysql> SHOW TRIGGERS [{FROM | IN} db_name] LIKE 'pattern' | WHERE expr]
mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS where trigger_name = 'trigger_name';
mysql> SHOW CREATE TRIGGER trigger_name;

4. 使用触发器

触发器在同一schema中必须具有唯一的名称，在不同的schema中触发器可以具有相同的名称。
同一表不能创建具有相同的触发事件和动作的多个触发器

触发器限制：

• 触发程序不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL（允许存储程序通过参数将数据返回触发程序）。
• 触发程序不能使用以显式或隐式方式开始或结束事务的语句，如START TRANSACTION、COMMIT或ROLLBACK。

使用OLD和NEW关键字，能够访问受触发器影响的行中的列（OLD和NEW不区分大小写）。在INSERT触发器中，仅能使用NEW.col_name，没有旧行;在DELETE触发器中，仅能使用OLD.col_name，没有新行;在UPDATE触发器中，可以使用OLD.col_name来引用更新前的行中的列，也能使用NEW.col_name来引用更新后的行中的列。用OLD命名的列是只读的。对于用NEW命名的列，如果具有SELECT权限可引用它。在BEFORE触发器中，如果你具有UPDATE权限，可使用“SET NEW.col_name = value”更改它的值。

在BEFORE触发程序中，AUTO_INCREMENT列的NEW值为0，而不是实际插入新记录时将自动生成的序列号。

通过使用BEGIN ... END结构，能够在触发器中定义执行多条语句。在BEGIN块中，还能使用存储程序中允许的其他语法，如条件和循环等。
但是需要重新定义语句分隔符，以便能够在触发器定义中使用字符“;”。

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
    ->     END IF;
    -> END;//
mysql> delimiter ;

较为简单的方法是，单独定义存储程序，然后使用CALL语句从触发器调用存储程序。

在触发器的执行过程中，MySQL处理错误的方式如下：

• 如果BEFORE触发器失败，不执行相应行上的操作。
• 仅当BEFORE触发器（如果有的话）和行操作均已成功执行，才执行AFTER触发器。
• 如果在BEFORE或AFTER触发器的执行过程中出现错误，将导致触发器调用的整个语句的失败。
• 对于事务性表，如果触发器失败（以及由此导致的整个语句的失败），该语句所执行的所有更改将回滚。对于非事务性表，不能执行这类回滚，因而，即使语句失败，失败之前所作的任何更改依然有效。

整理自网络

Svoid
2015-01-22