监视某个表的增删改查：触发器

触发器

触发器主要用于监视某个表的insert、update以及delete等更新操作，这些操作可以分别激活该表的insert、update或者delete类型的触发程序运行，从而实现数据的自动维护。

触发程序是与表有关的命名数据库对象，当表发生事件时，激活触发程序对象。如果定义了触发程序，一旦数据库执行事件语句时就会激发触发器执行相应的操作。

触发器是特殊的存储过程，都是嵌入到MySQL的一段程序。触发器由插入、更新和删除事件来触发某个操作。MySQL的触发事件有三种：

insert：将新记录插入表时激活触发程序，通过insert、load data和replace语句，可以激活触发程序运行。
update：更改某一行记录时激活触发程序，例如通过update语句，可以激活触发程序运行。
delete：从表中删除某一行记录时激活触发程序，通过delete和replace语句，可以激活触发程序运行。

创建触发器

创建一个或多个执行语句的触发器，语法如下：

CREATE  TRIGGER  名称  触发时机（before/after） 触发事件（INSERT/UPDATE/DELETE）
	ON 表名 FOR EACH ROW 触发程序体

before表示在触发事件发生之前执行触发程序；after表示在触发事件发生之后执行触发器；for each row表示更新（insert、update或者delete）操作影响的每一条记录都会执行一次触发程序。

CREATE TRIGGER tr_sum  AFTER  DELETE ON fruit  FOR  EACH  ROW  BEGIN 
		SET  @sum = @sum+NEW.f_price;
END//

【例5】创建一个包含多个执行语句的触发器

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
  a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
  b4 INT DEFAULT 0
);
/*创建触发器*/
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 //

查看触发器

SHOW TRIGGERS语句查看触发器信息

通过SHOW TRIGGERS \G查看触发器，适合触发器较少的情况。
在triggers表中查看触发器信息

所有触发器的定义都在INFORMATION_SCHEMA数据库的TRIGGERS表格中，可以通过查询命令SELECT来查看。

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
	WHERE TRIGGER_NAME = ‘触发器名称’\G;

删除触发器

使用DROP TRIGGER语句可以删除MySQL中已经定义的触发器，语法如下：

DROP TRIGGER 数据库名 触发器名；

使用触发器的10条注意事项：

1．触发程序中如果包含select语句，该select语句不能返回结果集。

2．同一个表不能创建两个相同触发时间、触发事件的触发程序。

3．触发程序中不能使用以显式或隐式方式打开、开始或结束事务的语句，如start transaction、commit、rollback或者set autocommit=0等语句。

4．MySQL触发器针对记录进行操作，当批量更新数据时，引入触发器会导致更新操作性能降低。

5．在MyISAM存储引擎中，触发器不能保证原子性。InnoDB存储引擎支持事务，使用触发器可以保证更新操作与触发程序的原子性，此时触发程序和更新操作是在同一个事务中完成。

6．InnoDB存储引擎实现外键约束关系时，建议使用级联选项维护外键数据；MyISAM存储引擎虽然不支持外键约束关系时，但可以使用触发器实现级联修改和级联删除，进而维护“外键”数据，模拟实现外键约束关系。

7．使用触发器维护InnoDB外键约束的级联选项时，数据库开发人员究竟应该选择after触发器还是before触发器？答案是：应该首先维护子表的数据，然后再维护父表的数据，否则可能出现错误。

8．MySQL的触发程序不能对本表进行更新语句（例如update语句）。触发程序中的更新操作可以直接使用set命令替代，否则可能出现错误信息，甚至陷入死循环。

9．在before触发程序中，auto_increment字段的new值为0，不是实际插入新记录时自动生成的自增型字段值。

10．添加触发器后，建议对其进行详细的测试，测试通过后再决定是否使用触发器。