存储过程之六—触发器

一、触发器

　　触发器是与表有关的命名数据库对象，当表上出现特定事件时，将调用该对象。它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。

　　触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。
触发器有一个非常好的特性就是:触发器可以禁止或回滚违反引用完整性的更改，从而取消所尝试的数据修改。

　　1、创建

　　　　语法：

　　　　CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

　　　　trigger_time:是触发器的动作时间。它可以是BEFORE或AFTER，以指明触发器是在激活它的语句之前或之后触发。

　　　　trigger_event: 指明了激活触发器的语句的类型。trigger_event可以是如下之中的一个：

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

　　对于具有相同触发器动作时间和事件的给定表，不能有两个触发器。如，对于某一表，不能有两个BEFORE UPDATE触发器。但可以有1个BEFORE UPDATE触发器和1个BEFORE INSERT触发器，或1个BEFORE UPDATE触发器和1个AFTER UPDATE触发器。

　　　　trigger_stmt：是当触发器激活时执行的语句。如果你打算执行多个语句，可使用BEGIN ... END复合语句结构。这样，能够定义执行多条语句的触发器。

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

　　2、查看

SHOW TRIGGERS; -- 查看所有触发器 
SHOW CREATE TRIGGER ins_sum;-- 查看具体触发器

　　3、删除　

DROP TRIGGER trigger_name; -- 删除具体触发器

　　注释：从MySQL 5.0.10之前的MySQL版本升级到5.0.10或更高版本时（包括所有的MySQL 5.1版本），必须在升级之前舍弃所有的触发器，并在随后重新创建它们，否则，在升级之后DROP TRIGGER不工作。

　　示例：

DROP TABLE IF EXISTS `account`;
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;-- 将插入amount列的值加起来。

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

执行了INSERT语句后，@sum的值是14.98 + 1937.50 – 100，或1852.48。
执行结果如下：

二、old与NEW

　　关键字new和OLD的区别：

INSERT：只有 NEW
UPDATE： BEFORE OLD 、 AFTER NEW
DELETE： 只有OLD

　　用OLD命名的列是只读的。你可以引用它，但不能更改它。对于用NEW命名的列，如果具有SELECT权限，可引用它。 可使用“SET NEW.col_name = value”更改它的值。这意味着，你可以使用触发器来更改将要插入到新行中的值，或用于更新行的值。OLD和NEW是对触发器的MySQL扩展。

三、实例

　　表结构如下：　

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

 　　1、insert 

DROP TRIGGER IF EXISTS trigger_insert;
-- before
CREATE TRIGGER trigger_insert BEFORE INSERT ON person
FOR EACH ROW  
SET @info = new.username, new.age = new.age + 2; 

INSERT INTO person (username, age, password) VALUES ( 'zhangsan', '21', '123456');
SELECT @info;

 　　插入之前被调用，age被加1，也记录了加入记录的username。insert 只有before才能修改new.age的值，after中，无法修改。

　　2、update 

DROP TRIGGER IF EXISTS trigger_update_before;
CREATE TRIGGER trigger_update_before BEFORE UPDATE ON person
FOR EACH ROW 
BEGIN
SET @infoname1 = new.username; -- 记录新名字
SET new.age = new.age + 3; -- 修改年龄
END;

DROP TRIGGER IF EXISTS trigger_update_after;
CREATE TRIGGER trigger_update_after AFTER UPDATE ON person
FOR EACH ROW  
SET @infoname2 =  old.username;  -- 记录修改前的名字

SELECT * FROM person WHERE id = 1;
UPDATE person SET username='lisi', age = 0 WHERE id = 1;
SELECT * FROM person WHERE id = 1;
SELECT @infoname1 AS newname, @infoname2 AS oldname;

　　执行完14行结果如下：

 　　执行完15、16行结果如下：

　　年龄已被触发器更改

　　执行完17行后结果如下：

　　触发器已获取更改前后的用户名称。

　　3、delete

DROP TRIGGER IF EXISTS trigger_delete_after;
CREATE TRIGGER trigger_delete_after AFTER DELETE ON person
FOR EACH ROW  
SET @infoname3 =  old.username;  -- 获取被删除的用户名称
 
DELETE FROM person WHERE id = 1;
SELECT * FROM person WHERE id = 1;
SELECT @infoname3 AS oldname;

　　执行完第7行结果如下：

　　记录已经被删除

　　执行完第8行结果如下：

　　显示已被删除的记录的用户名称。

　　4、触发器调用存储过程

　　在触发器中通过使用BEGIN ... END结构，能够定义执行多条语句的触发器。在BEGIN块中，还能使用其他语法，如条件和循环等。我们可以将这些语句封装到存储过程里面，供触发器调用。

　　实例如下：

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
INSERT INTO person (username, age, password) VALUES ( 'zhangsan', '21', '123456');

DROP PROCEDURE IF EXISTS pro_person_update;
CREATE PROCEDURE pro_person_update(
    INOUT age INT(11)
)
BEGIN
    IF age < 0 THEN -- 年龄小于0
        SET age = 0;
    ELSEIF age > 100 THEN -- 年龄大于100
        SET age= 100;
    END IF;
END;

DROP TRIGGER IF EXISTS trgger_proceduce_person_update;
CREATE TRIGGER trgger_proceduce_person_update BEFORE UPDATE ON person
FOR EACH ROW 
    CALL pro_person_update(new.age);

SELECT * FROM person WHERE id = 1;
UPDATE person SET age= -3 WHERE id=1;
SELECT * FROM person WHERE id = 1;

　　由于上面的三个例子生成的触发器会对本例有影响，所以重新创建表结构。

　　执行完第28行结果如下：

　　执行完第29，30行后，结果如下：

　　age为0，存储过程已经被update before触发器调用。

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

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