Mysql触发器

1、 触发器简介

　　触发器（trigger）是个特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。
触发器有一个非常好的特性就是:触发器可以禁止或回滚违反引用完整性的更改，从而取消所尝试的数据修改。

2、 触发器语法

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：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。

由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。

另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器。

案例： 

## 学生表
CREATE TABLE student (
    stuid int(11), ## 学生id
    classId int(11) ## 班级id
)ENGINE=INNODB;

## 班级表
CREATE TABLE class (
    classId int(11), ## 班级id
    stucount int(3)  ## 该班级学生的个数
)ENGINE=INNODB;

## 创建一个触发器， 添加学生后，班级表的学生数加一
DELIMITER ;;
CREATE TRIGGER tri_addStu AFTER INSERT ON student FOR EACH ROW BEGIN
        ## 先判断添加的班级 有没有， 没有就添加到班级表
        DECLARE t_classId INT;
        
        ## 注意 这里的 AS t_classId 必须和前面的变量名相同
        SET t_classId = (SELECT COUNT(classId) AS t_classId FROM  class WHERE classId=NEW.classId);
        IF t_classId = 0 THEN 
            INSERT INTO class VALUES (NEW.classId, 1);
        ELSE 
            UPDATE class SET stucount=stucount+1 WHERE classId=NEW.classId;
        END IF;
    END;;
DELIMITER ;

3. NEW和OLD详解

上述示例中使用了NEW关键字，MySQL 中定义了 NEW 和 OLD，用来表示触发器的所在表中，触发了触发器的那一行数据。

具体地：

在 INSERT 型触发器中，NEW 用来表示将要（BEFORE）或已经（AFTER）插入的新数据；

在 UPDATE 型触发器中，OLD 用来表示将要或已经被修改的原数据，NEW 用来表示将要或已经修改为的新数据；
在 DELETE 型触发器中，OLD 用来表示将要或已经被删除的原数据；
使用方法： NEW.columnName （columnName 为相应数据表某一列名）
另外，OLD 是只读的，而 NEW 则可以在触发器中使用 SET 赋值，这样不会再次触发触发器，造成循环调用（如每插入一个学生前，都在其学号前加“2013”）。

4. 查看触发器

SHOW TRIGGERS；查看全面触发器，这个跟show tables; show databases; 用法一样。

show triggers from test;   可以只看某个数据库的触发器。 test表示数据库名；

这个触发器就是我们前面建立的。每列的意思都很明显。下面介绍介绍几个不常用的。

sql_mode: 表示sql执行时，sql的严谨级别和规则。

Definer: 触发器的定义者。

5. 删除触发器

DROP TRIGGER [IF EXISTS] trigger_name; 这个跟删除表是一样的。

6.触发器的执行顺序

我们建立的数据库一般都是 InnoDB 数据库，其上建立的表是事务性表，也就是事务安全的。这时，若SQL语句或触发器执行失败，MySQL 会回滚事务，有：

①如果 BEFORE 触发器执行失败，SQL 无法正确执行。
②SQL 执行失败时，AFTER 型触发器不会触发。
③AFTER 类型的触发器执行失败，SQL 会回滚