十一、MySQL触发器

MySQL的触发器和存储过程一样，都是嵌入到MySQL的一段程序。触发器是由时间来触发某个操作，这些时间包括INSERT、UODATE和DELETE语句。如果定义了触发程序，当数据库执行这些语句的时候就会触发执行相应的操作，触发程序是与表有关的命名数据库对象，当表上出现特定事件时，当激活该对象。

11.1、创建触发器

触发器是一个特殊的存储过程，不同的是，执行存储过程要使用CALL语句来调用，而触发器的执行不需要使用CALL语句来调用，也不需要手工启动，只要当预定义的时间发生的时候，就会被MySQL自动调用。

创建一个触发器的语法为：

CREATE TRIGGER trigger_name trigger_time trigger_event    ON tbl_name FOR EACH ROW trigger_body

创建一个单执行语句的触发器：

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

创建多个执行语句的触发器的语法：

CREATE TRIGGER trigger_name trigger_time trigger_event        ON tbl_name FOR EACH ROW         BEGIN          trigger_stmt        END

11.2、查看触发器

查看触发器是指查看数据库中已存在的触发器的定义、状态和触发信息等可以通过SHOW TRUGGERS和在triggers表中查看触发器信息。

mysql> CREATE TABLE myevent    -> (    -> id int(11) DEFAULT NULL,    -> evt_name char(20) DEFAULT NULL    -> );Query OK, 0 rows affected (0.03 sec)mysql> CREATE TRIGGER trig_update AFTER UPDATE ON account    -> FOR EACH ROW INSERT INTO myevent VALUES (1,'AFTER UPDATE');Query OK, 0 rows affected (0.05 sec)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:              Definer: root@localhostcharacter_set_client: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci*************************** 2. row ***************************             Trigger: trig_update               Event: UPDATE               Table: account           Statement: INSERT INTO myevent VALUES (1,'AFTER UPDATE')              Timing: AFTER             Created: NULL            sql_mode:              Definer: root@localhostcharacter_set_client: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci2 rows in set (0.00 sec)

在MySQL中所有触发器的定义都存在INFORMATION_SCHEMA数据库的TRIGGERS表中，可以通过SELECT 来查看，其语法格式为：

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition;

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='trig_update' \G*************************** 1. row ***************************           TRIGGER_CATALOG: def            TRIGGER_SCHEMA: test              TRIGGER_NAME: trig_update        EVENT_MANIPULATION: UPDATE      EVENT_OBJECT_CATALOG: def       EVENT_OBJECT_SCHEMA: test        EVENT_OBJECT_TABLE: account              ACTION_ORDER: 0          ACTION_CONDITION: NULL          ACTION_STATEMENT: INSERT INTO myevent VALUES (1,'AFTER UPDATE')        ACTION_ORIENTATION: ROW             ACTION_TIMING: AFTERACTION_REFERENCE_OLD_TABLE: NULLACTION_REFERENCE_NEW_TABLE: NULL  ACTION_REFERENCE_OLD_ROW: OLD  ACTION_REFERENCE_NEW_ROW: NEW                   CREATED: NULL                  SQL_MODE:                    DEFINER: root@localhost      CHARACTER_SET_CLIENT: utf8      COLLATION_CONNECTION: utf8_general_ci        DATABASE_COLLATION: utf8_general_ci1 row in set (0.00 sec)

11.3、触发器的使用

触发程序是与表有关的命名数据库对象，当表上出现特定时间时，将激活该对象。

创建一个在account表插入数据之后，更新myevent数据表的触发器。

mysql> CREATE TRIGGER trig_inster AFTER INSERT ON account     -> FOR EACH ROW INSERT INTO myevent VALUES (2,'AFTER INSERT'); Query OK, 0 rows affected (0.02 sec)mysql> INSERT INTO account VALUES (1,1.00),(2,2.00);Query OK, 2 rows affected (0.01 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> SELECT * FROM myevent;+------+--------------+| id   | evt_name     |+------+--------------+|    2 | AFTER INSERT ||    2 | AFTER INSERT |+------+--------------+2 rows in set (0.00 sec)

11.4、删除触发器

使用DROP TRIGGER语句可以删除MySQL中已经存在的触发器，其语法格式为：

DROP TRIGGER [schema_name.]trigger_name

删除一个触发器

mysql> DROP TRIGGER test.ins_sum;Query OK, 0 rows affected (0.02 sec)