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_bodytrigger_name：触发器名称，用户自行指定 trigger_time：触发时机，可以指定为before或after trigger_event：标识触发事件 tbl_name：标识触发器的表名 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: 0
mysql> 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
END11.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@localhost
character_set_client: utf8
collation_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@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
2 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: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_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_ci
1 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: 0
mysql> 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)
