MySQL知识（十八）——MySQL触发器

1 创建触发器

　　MySQL的触发器（trigger）和存储过程一样，都是嵌入到MySQL的一段程序。触发器是由事件来触发某个操作，这些时间包括INSERT、UPDATE和DELETE语句。如果定义了触发程序，当数据库执行这些语句的时候就会激发触发器执行相应的操作，触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。
　　触发器可以查询其他表，而且可以包含复杂的SQL语句。它们主要用于满足复杂的业务规则或要求。例如：可以根据客户当前的账户状态，控制是否允许插入新订单。

CREATE TRIGGER trigger_name trigger_time trigger_eventON tbl_name FOR EACH ROW trigger_stmt

• trigger_name：自定义触发器名称
• trigger_time：触发时机，可以指定为before或after
• trigger_event：触发事件，包括INSERT、UPDATE和DELETE
• tbl_name：建立触发器的表名，即在哪张表上建立触发器
• trigger_stmt：触发器程序体，触发器程序可以使用begin和end作为开始和结束，中间包含多条语句。

1.1 只有一个执行语句的触发器

mysql> CREATE TABLE account(acct_num INT,amount DECIMAL(10,2));Query OK, 0 rows affectedmysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account    -> FOR EACH ROW SET @sum=@sum+NEW.amount;Query OK, 0 rows affectedmysql> SET @sum=0;Query OK, 0 rows affectedmysql> INSERT INTO account VALUES(1,1.00),(2,2.00);Query OK, 2 rows affectedRecords: 2  Duplicates: 0  Warnings: 0mysql> SELECT @sum;+------+| @sum |+------+| 3.00 |+------+1 row in set

1.2 有多个执行语句的触发器

/*创建4个测试数据表*/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//DELIMITER ;/*分别向test3和test4表中插入测试数据*/INSERT INTO test3 (a3)VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL);INSERT INTO test4 (a4) VALUES  (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);/*向test1表中插入数据，激活触发器的调用事件调用触发器*/INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);

查看触发器执行后的结果

mysql> SELECT * FROM test1;+----+| a1 |+----+|  1 ||  3 ||  1 ||  7 ||  1 ||  8 ||  4 ||  4 |+----+8 rows in setmysql> SELECT * FROM test2;+----+| a2 |+----+|  1 ||  3 ||  1 ||  7 ||  1 ||  8 ||  4 ||  4 |+----+8 rows in setmysql> SELECT * FROM test3;+----+| a3 |+----+|  2 ||  5 ||  6 ||  9 || 10 |+----+5 rows in setmysql> SELECT * FROM test4;+----+----+| a4 | b4 |+----+----+|  1 |  3 ||  2 |  0 ||  3 |  1 ||  4 |  2 ||  5 |  0 ||  6 |  0 ||  7 |  1 ||  8 |  1 ||  9 |  0 || 10 |  0 |+----+----+10 rows in set

2 查看触发器

2.1 SHOW TRIGGERS语句

mysql> SHOW TRIGGERS \G;*************************** 1. row ***************************             Trigger: ins_sum               Event: INSERT               Table: account           Statement: SET @sum=@sum+NEW.amount              Timing: BEFORE             Created: NULL            sql_mode: NO_ENGINE_SUBSTITUTION             Definer: root@localhostcharacter_set_client: utf8collation_connection: utf8_general_ci  Database Collation: latin1_swedish_ci*************************** 2. row ***************************             Trigger: testref               Event: INSERT               Table: test1           Statement: 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              Timing: BEFORE             Created: NULL            sql_mode: NO_ENGINE_SUBSTITUTION             Definer: root@localhostcharacter_set_client: utf8collation_connection: utf8_general_ci  Database Collation: latin1_swedish_ci2 rows in set (0.01 sec)

2.2 SHOW TRIGGERS语句

可以根据条件condition，查询特定的触发器信息；如果没有WHERE子句，则查询所有触发器信息：

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS [WHERE condition];

例如：

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='ins_sum' \G;*************************** 1. row ***************************           TRIGGER_CATALOG: def            TRIGGER_SCHEMA: test              TRIGGER_NAME: ins_sum        EVENT_MANIPULATION: INSERT      EVENT_OBJECT_CATALOG: def       EVENT_OBJECT_SCHEMA: test        EVENT_OBJECT_TABLE: account              ACTION_ORDER: 0          ACTION_CONDITION: NULL          ACTION_STATEMENT: SET @sum=@sum+NEW.amount        ACTION_ORIENTATION: ROW             ACTION_TIMING: BEFOREACTION_REFERENCE_OLD_TABLE: NULLACTION_REFERENCE_NEW_TABLE: NULL  ACTION_REFERENCE_OLD_ROW: OLD  ACTION_REFERENCE_NEW_ROW: NEW                   CREATED: NULL                  SQL_MODE: NO_ENGINE_SUBSTITUTION                   DEFINER: root@localhost      CHARACTER_SET_CLIENT: utf8      COLLATION_CONNECTION: utf8_general_ci        DATABASE_COLLATION: latin1_swedish_ci1 row in set (0.06 sec)

3 触发器的使用

触发程序可以设置在语句之前或之后激活，如上面示例。

4 删除触发器

DROP TRIGGER [schema_name.]trigger_name

schema_name：数据库名称，可选。默认是从当前数据库中舍弃触发程序。

mysql> DROP TRIGGER ins_sum;Query OK, 0 rows affectedmysql> DROP TRIGGER testref;Query OK, 0 rows affected

5 注意

　　（1）对于相同的表，相同的事件创建一个触发器。例如，只能对表stu创建了一个BEFORE INSERT触发器。
　　（2）及时删除不再使用的触发器。如果需求发生变化，而触发器没有进行相应改变，那么仍会执行旧的语句。从而会影响新的数据完整性。