mysql的trigger触发器用法

1. TRIGGER简介

trigger是mysql的触发器，
当建立触发器时，会监控特定的事件，当触发时会激活对象，处理trigger_stmt，即要执行的语句。
比如表的新增一条记录时，你需要更新记录的值得总和，则可以用trigger来实现。

1. Trigger创建语句

[plain] view plain copy

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：将新行插入表时激活触发程序，例如，通过INSERT、LOAD DATA和REPLACE语句。
    UPDATE：更改某一行时激活触发程序，例如，通过UPDATE语句。
    DELETE：从表中删除某一行时激活触发程序，例如，通过DELETE和REPLACE语句。
tbl_name : 操作表名称
trigger_stmt: 触发器要操作的语句

1. 使用限制

1）同一个表不能有同一种触发器：
例如，对于某一表，不能有两个BEFORE UPDATE触发程序。但可以有1个BEFORE UPDATE触发程序和1个BEFORE INSERT触发程序，或1个BEFORE UPDATE触发程序和1个AFTER UPDATE触发程序。
2）必须是永久表，不能是TEMPORARY表或视图。

1. trigger使用

创建表和触发器
[plain] view plain copy

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;  

NEW关键词为操作后的新值。
OLD表示操作之前的值。
插入三行记录，读取@sum的值
[plain] view plain copy

mysql> SET @sum = 0;  
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);  
mysql> SELECT @sum AS 'Total amount inserted';  

返回值为 @sum的值是14.98 + 1937.50 – 100 = 1852.48。

对于触发程序是多行，使用BEGIN … END结构
[plain] view plain copy

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 ;  

更新一条记录，查看值：
[plain] view plain copy

update account set acct_num = 241 where acct_num=141;    

由于上面的update触发了trigger，会使 amount的值设置为100.

1. 查看已经建立的trigger
   [plain] view plain copy

   show triggers

还可以在information_schema数据库的 triggers表中查看。

1. 删除触发器
   [plain] view plain copy

   drop trigger test.ins_sum;

test为数据库名，ins_sum为触发器名称

1. 对失败的处理

· 如果BEFORE触发程序失败，不执行相应行上的操作。
· 仅当BEFORE触发程序（如果有的话）和行操作均已成功执行，才执行AFTER触发程序。
· 如果在BEFORE或AFTER触发程序的执行过程中出现错误，将导致调用触发程序的整个语句的失败。

· 对于事务性表，如果触发程序失败（以及由此导致的整个语句的失败），该语句所执行的所有更改将回滚。对于非事务性表，不能执行这类回滚，因而，即使语句失败，失败之前所作的任何更改依然有效。