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

1 创建触发器

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

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：触发器程序体，触发器程序可以使用begin和end作为开始和结束，中间包含多条语句。

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

mysql> CREATE TABLE account(acct_num INT,amount DECIMAL(10,2));
Query OK, 0 rows affected

mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum=@sum+NEW.amount;
Query OK, 0 rows affected

mysql> SET @sum=0;
Query OK, 0 rows affected

mysql> INSERT INTO account VALUES(1,1.00),(2,2.00);
Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

mysql> 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 set

mysql> SELECT * FROM test2;
+----+
| a2 |
+----+
|  1 |
|  3 |
|  1 |
|  7 |
|  1 |
|  8 |
|  4 |
|  4 |
+----+
8 rows in set

mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
|  2 |
|  5 |
|  6 |
|  9 |
| 10 |
+----+
5 rows in set

mysql> 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@localhost
character_set_client: utf8
collation_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@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci
2 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: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_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_ci
1 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 affected

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

5 注意

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