触发器是与表有关的数据库对象，在满足定义条件时触发，并执行触发器中定义的语句集合。
注：触发器只能创建在永久表上，不能对临时表创建触发器。
对同一个表相同触发器的相同触发事件，只能定义一个触发器。例如：对某个表的不同字段的after更新触发器，在使用oracle数据库的时候可以定义成两个不同的update触发器，更新不同的字段时触发单独的触发器，但在mysql数据库中，只能定义成一个触发器，在触发器中通过判断更新的字段进行对应处理。
使用别名old和new来引用触发器中发生变化的记录内容，这与其他的数据库是相似的，现在的触发器还支持行级触发，不支持语句级触发。
例子：
创建表t14,t15，并在表t14创建触发器，当向表t14插入数据的时候，也向表t15插入数据。
mysql> create table t14(id int(11),name varchar(20),age int(3));
Query OK, 0 rows affected (0.31 sec)
mysql> create table t15(id int(11),grade int(3));
Query OK, 0 rows affected (0.28 sec)
mysql> delimiter $$
mysql> create trigger ins_t14
-> after insert on t14 for each row begin
-> insert into t15(id,grade)values(1,1);
-> end;$$
Query OK, 0 rows affected (0.06 sec)
mysql> delimiter ;
mysql> insert into t14(id,name,age)values(1,'fzy1',1);
Query OK, 1 row affected (0.07 sec)
mysql> select * from t15;
+------+-------+
| id | grade |
+------+-------+
| 1 | 1 |
+------+-------+
1 row in set (0.00 sec)
由此，我们可以看到，当向t14插入数据的时候，确实触发了事件。
注： “delimiter $$ ” 语句用于更改结束符号。本来是以;为一个语句结束，现在改为“$$”作为结束，记得最后将“$$”改为;。
对于Insert into ...on duplicate key update...语句来说，触发器的顺序可能会造成疑惑，下面对t14分别创建before insert ,after insert ,before update ,after update触发器，然后插入记录，观察触发器的触发情况。
我们先删除ins_t14这个触发器：
mysql> drop trigger ins_t14;
Query OK, 0 rows affected (0.00 sec)
然后分别创建以上几种触发器：
mysql> delimiter $$
mysql> create trigger ins_t14_before
-> before insert on t14 for each row begin
-> insert into t15(text)values('before insert');
-> end;
-> $$
Query OK, 0 rows affected (0.10 sec)
mysql> create trigger ins_t14_aft
-> after insert on t14 for each row begin
-> insert into t15(text)values('after insert');
-> end;
-> $$
Query OK, 0 rows affected (0.06 sec)
mysql> create trigger upd_t14_bef
-> before update on t14 for each row begin
-> insert into t15(text)values('before update');
-> end;
-> $$
Query OK, 0 rows affected (0.12 sec)
mysql> create trigger upd_t14_aft
-> after update on t14 for each row begin
-> insert into t15(text)values('after update');
-> end;
-> $$
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t14(id,name,age)values(2,'fzy2',2);
Query OK, 1 row affected (0.04 sec)
mysql> select * from t15;
+------+-------+---------------+
| id | grade | text |
+------+-------+---------------+
| NULL | NULL | before insert |
| NULL | NULL | after insert |
+------+-------+---------------+
2 rows in set (0.00 sec)
mysql> update t14 set age=3 where id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t15;
+------+-------+---------------+
| id | grade | text |
+------+-------+---------------+
| NULL | NULL | before insert |
| NULL | NULL | after insert |
| NULL | NULL | before update |
| NULL | NULL | after update |
+------+-------+---------------+
4 rows in set (0.00 sec)
二，删除触发器
上面例子中已经使用过了删除触发器，之间考下来看一下应该就能明白了：
mysql> drop trigger ins_t14;
Query OK, 0 rows affected (0.00 sec)三，查看触发器
可通过show triggers命令来查看触发器的状态，语法等信息，但因为不能查询指定的触发器，所以每次返回所有的触发器信息，使用起来不是很方便
mysql> show triggers \G;
*************************** 1. row ***************************
Trigger: ins_t14_before
Event: INSERT
Table: t14
Statement: begin insert into t15(text)values('before insert'); end
Timing: BEFORE
Created: 2016-10-04 03:32:51.04
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 2. row ***************************
Trigger: ins_t14_aft
Event: INSERT
Table: t14
Statement: begin insert into t15(text)values('after insert'); end
Timing: AFTER
Created: 2016-10-04 03:33:20.82
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 3. row ***************************
Trigger: upd_t14_bef
Event: UPDATE
Table: t14
Statement: begin
insert into t15(text)values('before update');
end
Timing: BEFORE
Created: 2016-10-04 03:30:33.36
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 4. row ***************************
Trigger: upd_t14_aft
Event: UPDATE
Table: t14
Statement: begin
insert into t15(text)values('after update');
end
Timing: AFTER
Created: 2016-10-04 03:34:39.65
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
4 rows in set (0.00 sec)
ERROR:
No query specified
另一种方法是使用information_shema.triggers这个表，这个方式查询指定触发器信息，操作起来要方便很多
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> desc triggers;
+----------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------+------+-----+---------+-------+
| TRIGGER_CATALOG | varchar(512) | NO | | | |
| TRIGGER_SCHEMA | varchar(64) | NO | | | |
| TRIGGER_NAME | varchar(64) | NO | | | |
| EVENT_MANIPULATION | varchar(6) | NO | | | |
| EVENT_OBJECT_CATALOG | varchar(512) | NO | | | |
| EVENT_OBJECT_SCHEMA | varchar(64) | NO | | | |
| EVENT_OBJECT_TABLE | varchar(64) | NO | | | |
| ACTION_ORDER | bigint(4) | NO | | 0 | |
| ACTION_CONDITION | longtext | YES | | NULL | |
| ACTION_STATEMENT | longtext | NO | | NULL | |
| ACTION_ORIENTATION | varchar(9) | NO | | | |
| ACTION_TIMING | varchar(6) | NO | | | |
| ACTION_REFERENCE_OLD_TABLE | varchar(64) | YES | | NULL | |
| ACTION_REFERENCE_NEW_TABLE | varchar(64) | YES | | NULL | |
| ACTION_REFERENCE_OLD_ROW | varchar(3) | NO | | | |
| ACTION_REFERENCE_NEW_ROW | varchar(3) | NO | | | |
| CREATED | datetime(2) | YES | | NULL | |
| SQL_MODE | varchar(8192) | NO | | | |
| DEFINER | varchar(93) | NO | | | |
| CHARACTER_SET_CLIENT | varchar(32) | NO | | | |
| COLLATION_CONNECTION | varchar(32) | NO | | | |
| DATABASE_COLLATION | varchar(32) | NO | | | |
+----------------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
mysql> select * from triggers \G;
*************************** 1. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: sys
TRIGGER_NAME: sys_config_insert_set_user
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: sys
EVENT_OBJECT_TABLE: sys_config
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; END
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: 2016-09-25 19:38:11.69
SQL_MODE:
DEFINER: mysql.sys@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
*************************** 2. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: sys
TRIGGER_NAME: sys_config_update_set_user
EVENT_MANIPULATION: UPDATE
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: sys
EVENT_OBJECT_TABLE: sys_config
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; END
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: 2016-09-25 19:38:11.79
SQL_MODE:
DEFINER: mysql.sys@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
*************************** 3. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: test1
TRIGGER_NAME: ins_t14_before
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: test1
EVENT_OBJECT_TABLE: t14
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: begin insert into t15(text)values('before insert'); end
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: 2016-10-04 03:32:51.04
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: latin1_swedish_ci
*************************** 4. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: test1
TRIGGER_NAME: ins_t14_aft
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: test1
EVENT_OBJECT_TABLE: t14
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: begin insert into t15(text)values('after insert'); end
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: 2016-10-04 03:33:20.82
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: latin1_swedish_ci
*************************** 5. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: test1
TRIGGER_NAME: upd_t14_bef
EVENT_MANIPULATION: UPDATE
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: test1
EVENT_OBJECT_TABLE: t14
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: begin
insert into t15(text)values('before update');
end
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: 2016-10-04 03:30:33.36
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: latin1_swedish_ci
*************************** 6. row ***************************
TRIGGER_CATALOG: def
TRIGGER_SCHEMA: test1
TRIGGER_NAME: upd_t14_aft
EVENT_MANIPULATION: UPDATE
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: test1
EVENT_OBJECT_TABLE: t14
ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: begin
insert into t15(text)values('after update');
end
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: 2016-10-04 03:34:39.65
SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: latin1_swedish_ci
6 rows in set (0.01 sec)
ERROR:
No query specified
四，触发器的使用
触发器的执行有以下两个限制
1，触发程序不能调用将数据返回客户端的存储程序，也不能采用call语句的动态sql语句，但是允许存储程序通过参数将数据返回触发程序，也就是存储过程或者函数通过out或者inout类型的参数将数据返回触发器是可以的，但是不能调用直接返回数据的过程。
2，不能在触发器中使用以显式或隐式方式开始或结束事务的语句，如start transction ,commit 或rollback。
992
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
