MySQL中触发器的基础学习教程
0.触发器的基本概念
触发器是一种特殊的存储过程，它在插入，删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和更复杂的数据控制能力。
数据库触发器有以下的作用：
(1).安全性。可以基于数据库的值使用户具有操作数据库的某种权利。
#可以基于时间限制用户的操作，例如不允许下班后和节假日修改数据库数据。
#可以基于数据库中的数据限制用户的操作，例如不允许股票的价格的升幅一次超过10%。
(2).审计。可以跟踪用户对数据库的操作。
#审计用户操作数据库的语句。
#把用户对数据库的更新写入审计表。
(3).实现复杂的数据完整性规则
#实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可以引用列或数据库对象。例如，触发器可回退任何企图吃进超过自己保证金的期货。
#提供可变的缺省值。
(4).实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。例如，在auths表author_code列上的删除触发器可导致相应删除在其它表中的与之匹配的行。
#在修改或删除时级联修改或删除其它表中的与之匹配的行。
#在修改或删除时把其它表中的与之匹配的行设成NULL值。
#在修改或删除时把其它表中的与之匹配的行级联设成缺省值。
#触发器能够拒绝或回退那些破坏相关完整性的变化，取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时，这种触发器会起作用。例如，可以在books.author_code列上生成一个插入触发器，如果新值与auths.author_code列中的某值不匹配时，插入被回退。
(5).同步实时地复制表中的数据。
(6).自动计算数据值，如果数据的值达到了一定的要求，则进行特定的处理。例如，如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。
1.创建触发器语法
CREATE
[DEFINER={user|CURRENT_USER}]
TRIGGERtrigger_name
trigger_timetrigger_event
ONtbl_nameFOREACHROW
trigger_body
trigger_time:{BEFORE|AFTER}
trigger_event:{INSERT|UPDATE|DELETE}
CREATE
[DEFINER={user|CURRENT_USER}]
TRIGGERtrigger_name
trigger_timetrigger_event
ONtbl_nameFOREACHROW
trigger_body
trigger_time:{BEFORE|AFTER}
trigger_event:{INSERT|UPDATE|DELETE}
语法相关部分说明：
1.1授权与回收
创建触发器需要有CREATETRIGGER权限：
grantcreatetriggeron`database_naem`.`table_name`to`user_name`@`ip_address`; grantcreatetriggeron`database_naem`.`table_name`to`user_name`@`ip_address`;
权限收回：
revokecreatetriggeron`database_naem`.`table_name`from`user_name`@`ip_address`; revokecreatetriggeron`database_naem`.`table_name`from`user_name`@`ip_address`;
1.2trigger_name
必须给触发器命令，最多64个字符，建议用表的名字_触发器类型的缩写方法命名。如ttlsa_posts_bi(表ttlsa_posts，触发器发生在insert之前before)
1.3DEFINER子句
在激活触发器时，检查访问权限，确保触发器安全使用。
1.4trigger_time
定义触发器触发时间。可以设置为在行记录更改之前或之后发生。
1.5trigger_event
定义触发器触发事件。触发的事件有：
1.5.1
INSERT：当一个新行插入到表中时触发。如INSERT、LOADDATA和REPLACE语句。
UPDATE：当一个行数据被更改时触发。如UPDATE语句。
DELETE：当一个行从表中删除时触发。如DELETE和REPLACE语句。注意：DROPTABLE和TRUNCATETABLE语句不会触发该触发器，因为它们不是使用DELETE。同样删除一个分区表也不会触发。
有一个潜在的混乱情况，如INSERTINTO...ONDUPLICATEKEYUPDATE...取决于是否有重复键行。
不能对一个表创建具有相同的触发事件和触发时间的多个触发器。如对于一个表不能创建两个BEFOREUPDATE触发器，但是，可以创建一个BEFOREUPDATE和一个BEFOREINSERT或一个BEFOREUPDATE和一个AFTERUPDATE触发器。
1.6FOREACHROW子句
定义触发执行间隔。FOREACHROW子句定义触发器每隔一行执行一次动作，而不是对整个表执行一次。
1.7trigger_body子句
包含要触发执行的SQL语句。可以是任何合法的语句，包括复合语句(需要使用BEGIN...END结构)，流控制语句(if、case、while、loop、for、repeat、leave、iterate)，变量声明(declare)以及指派(set)，异常处理声明，允许条件声明，但是这里的语句受的限制和函数的一样。
1.7.1OLD与NEW
在触发器的SQL语句中，可以关联表中的任何列，通过使用OLD和NEW列名来标识，如OLD.col_name、NEW.col_name。OLD.col_name关联现有的行的一列在被更新或删除前的值。NEW.col_name关联一个新行的插入或更新现有的行的一列的值。
对于INSERT语句，只有NEW是合法的。否则会报错：ERROR1363(HY000):ThereisnoOLDrowinonINSERTtrigger
对于DELETE语句，只有OLD是合法的。否则会报错：ERROR1363(HY000):ThereisnoNEWrowinonDELETEtrigger
对于UPDATE语句，NEW和OLD可以同时使用。
2.实例
2.1创建表
使用在《mysqludf_json将关系数据以JSON编码》一文中创建的表。后续会将用户表迁移到nosql数据库上的。
mysql>createtable`ttlsa_users`( ->`uid`int(11)unsigned, ->`username`varchar(40)NOTNULL, ->`password`varchar(40)NOTNULL, ->`createtime`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP, ->PRIMARYKEY(`uid`) ->); mysql>createtable`ttlsa_users`( ->`uid`int(11)unsigned, ->`username`varchar(40)NOTNULL, ->`password`varchar(40)NOTNULL, ->`createtime`timestampNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP, ->PRIMARYKEY(`uid`) ->);
创建另外一张表来存放触发器动作数据。
mysql>createtable`ttlsa_users3`( ->`uid`int(11)unsigned, ->`userinfo`varchar(200), ->); mysql>createtable`ttlsa_users3`( ->`uid`int(11)unsigned, ->`userinfo`varchar(200), ->);
2.2创建触发器
mysql>delimiter// mysql>createtriggerttlsa_users_ai ->afterinsertonttlsa_users ->foreachrow ->insertintottlsa_users3(uid,userinfo)values(uid,json_object(NEW.uid,NEW.username,NEW.password)); ->// mysql>createtriggerttlsa_users_au ->afterupdateonttlsa_users ->foreachrow ->updatettlsa_users3setuserinfo=json_object(NEW.uid,NEW.username,NEW.password)whereuid=OLD.uid; ->// mysql>delimiter// mysql>createtriggerttlsa_users_ai ->afterinsertonttlsa_users ->foreachrow ->insertintottlsa_users3(uid,userinfo)values(uid,json_object(NEW.uid,NEW.username,NEW.password)); ->// mysql>createtriggerttlsa_users_au ->afterupdateonttlsa_users ->foreachrow ->updatettlsa_users3setuserinfo=json_object(NEW.uid,NEW.username,NEW.password)whereuid=OLD.uid; ->//
2.3测试
mysql>insertintottlsa_usersvalues(890,'xuhh',md5('abc'),NULL,'testtrigger')//
QueryOK,1rowaffected(0.01sec)
mysql>select*fromttlsa_users//
+-----+-------------+----------------------------------+---------------------+------------------------------------+ |uid|username|password|createtime|json_data| +-----+-------------+----------------------------------+---------------------+------------------------------------+ |888|ttlsa_admin|6a6e41c9b741f740cfa5f266b249d452|2013-08-1011:27:01|\website\-"http://www.ttlsa.com"| |889|ttlsa_admin|6a6e41c9b741f740cfa5f266b249d452|2013-08-1014:08:44|xuhh| |890|xuhh|900150983cd24fb0d6963f7d28e17f72|2013-08-1416:40:49|testtrigger| +-----+-------------+----------------------------------+---------------------+------------------------------------+ 3rowsinset(0.00sec)
mysql>select*fromttlsa_users3//
+-----------------------------------------------------------------------------+------+
|userinfo|uid|
+-----------------------------------------------------------------------------+------+
|{"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"}|890|
+-----------------------------------------------------------------------------+------+
2rowsinset(0.00sec)
mysql>updatettlsa_userssetpassword='test_update'whereuid=890//
QueryOK,1rowaffected(0.00sec) Rowsmatched:1Changed:1Warnings:0
mysql>select*fromttlsa_users//
+-----+-------------+----------------------------------+---------------------+------------------------------------+ |uid|username|password|createtime|json_data| +-----+-------------+----------------------------------+---------------------+------------------------------------+ |888|ttlsa_admin|6a6e41c9b741f740cfa5f266b249d452|2013-08-1011:27:01|\website\-"http://www.ttlsa.com"| |889|ttlsa_admin|6a6e41c9b741f740cfa5f266b249d452|2013-08-1014:08:44|xuhh| |890|xuhh|test_update|2013-08-1416:41:33|testtrigger| +-----+-------------+----------------------------------+---------------------+------------------------------------+ 3rowsinset(0.00sec)
mysql>select*fromttlsa_users3//
+-----------------------------------------------------------------------------+------+
|userinfo|uid|
+-----------------------------------------------------------------------------+------+
|{"uid":890,"username":"xuhh","password":"test_update"}|890|
+-----------------------------------------------------------------------------+------+
2rowsinset(0.00sec)
mysql>insertintottlsa_usersvalues(890,'xuhh',md5('abc'),NULL,'testtrigger')//
QueryOK,1rowaffected(0.01sec)
mysql>select*fromttlsa_users//
+-----+-------------+----------------------------------+---------------------+------------------------------------+ |uid|username|password|createtime|json_data| +-----+-------------+----------------------------------+---------------------+------------------------------------+ |888|ttlsa_admin|6a6e41c9b741f740cfa5f266b249d452|2013-08-1011:27:01|\website\-"http://www.ttlsa.com"| |889|ttlsa_admin|6a6e41c9b741f740cfa5f266b249d452|2013-08-1014:08:44|xuhh| |890|xuhh|900150983cd24fb0d6963f7d28e17f72|2013-08-1416:40:49|testtrigger| +-----+-------------+----------------------------------+---------------------+------------------------------------+ 3rowsinset(0.00sec)
mysql>select*fromttlsa_users3//
+-----------------------------------------------------------------------------+------+
|userinfo|uid|
+-----------------------------------------------------------------------------+------+
|{"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"}|890|
+-----------------------------------------------------------------------------+------+
2rowsinset(0.00sec)
mysql>updatettlsa_userssetpassword='test_update'whereuid=890//
QueryOK,1rowaffected(0.00sec) Rowsmatched:1Changed:1Warnings:0
mysql>select*fromttlsa_users//
+-----+-------------+----------------------------------+---------------------+------------------------------------+ |uid|username|password|createtime|json_data| +-----+-------------+----------------------------------+---------------------+------------------------------------+ |888|ttlsa_admin|6a6e41c9b741f740cfa5f266b249d452|2013-08-1011:27:01|\website\-"http://www.ttlsa.com"| |889|ttlsa_admin|6a6e41c9b741f740cfa5f266b249d452|2013-08-1014:08:44|xuhh| |890|xuhh|test_update|2013-08-1416:41:33|testtrigger| +-----+-------------+----------------------------------+---------------------+------------------------------------+ 3rowsinset(0.00sec)
mysql>select*fromttlsa_users3//
+-----------------------------------------------------------------------------+------+
|userinfo|uid|
+-----------------------------------------------------------------------------+------+
|{"uid":890,"username":"xuhh","password":"test_update"}|890|
+-----------------------------------------------------------------------------+------+
2rowsinset(0.00sec)
3.管理
3.1列出触发器
mysql>SHOWTRIGGERSlike'%ttlsa%';触发器名称匹配%ttlsa%
***************************1.row*************************** Trigger:ttlsa_users_ai Event:INSERT Table:ttlsa_users Statement:insertintottlsa_users3(uid,userinfo)values(NEW.uid,json_object(NEW.uid,NEW.username,NEW.password)) Timing:AFTER Created:NULL sql_mode:NO_ENGINE_SUBSTITUTION Definer:root@127.0.0.1 character_set_client:utf8 collation_connection:utf8_general_ci DatabaseCollation:latin1_swedish_ci ***************************2.row*************************** Trigger:ttlsa_users_au Event:UPDATE Table:ttlsa_users Statement:updatettlsa_users3setuserinfo=json_object(NEW.uid,NEW.username,NEW.password)whereuid=OLD.uid Timing:AFTER Created:NULL sql_mode:NO_ENGINE_SUBSTITUTION Definer:root@127.0.0.1 character_set_client:utf8 collation_connection:utf8_general_ci DatabaseCollation:latin1_swedish_ci 2rowsinset(0.00sec)
mysql>SHOWTRIGGERS;#列出所有 mysql>SHOWTRIGGERSfromdatabase_name;#列出数据库的触发器 mysql>SHOWCREATETRIGGERtrigger_name;#查看创建触发器
***************************1.row*************************** Trigger:ttlsa_users_ai sql_mode:NO_ENGINE_SUBSTITUTION SQLOriginalStatement:CREATEDEFINER=`root`@`127.0.0.1`triggerttlsa_users_aiafterinsertonttlsa_usersforeachrowinsertintottlsa_users3(uid,userinfo)values(NEW.uid,json_object(NEW.uid,NEW.username,NEW.password)) character_set_client:utf8 collation_connection:utf8_general_ci DatabaseCollation:latin1_swedish_ci 1rowinset(0.01sec)
3.2INFORMATION_SCHEMA.TRIGGERS表
sql>SHOWTRIGGERSlike'%ttlsa%';#触发器名称匹配%ttlsa%
***************************1.row*************************** Trigger:ttlsa_users_ai Event:INSERT Table:ttlsa_users Statement:insertintottlsa_users3(uid,userinfo)values(NEW.uid,json_object(NEW.uid,NEW.username,NEW.password)) Timing:AFTER Created:NULL sql_mode:NO_ENGINE_SUBSTITUTION Definer:root@127.0.0.1 character_set_client:utf8 collation_connection:utf8_general_ci DatabaseCollation:latin1_swedish_ci ***************************2.row*************************** Trigger:ttlsa_users_au Event:UPDATE Table:ttlsa_users Statement:updatettlsa_users3setuserinfo=json_object(NEW.uid,NEW.username,NEW.password)whereuid=OLD.uid Timing:AFTER Created:NULL sql_mode:NO_ENGINE_SUBSTITUTION Definer:root@127.0.0.1 character_set_client:utf8 collation_connection:utf8_general_ci DatabaseCollation:latin1_swedish_ci 2rowsinset(0.00sec)
mysql>SHOWTRIGGERS;#列出所有 mysql>SHOWTRIGGERSfromdatabase_name;#列出数据库的触发器 mysql>SHOWCREATETRIGGERtrigger_name;#查看创建触发器
***************************1.row*************************** Trigger:ttlsa_users_ai sql_mode:NO_ENGINE_SUBSTITUTION SQLOriginalStatement:CREATEDEFINER=`root`@`127.0.0.1`triggerttlsa_users_aiafterinsertonttlsa_usersforeachrowinsertintottlsa_users3(uid,userinfo)values(NEW.uid,json_object(NEW.uid,NEW.username,NEW.password)) character_set_client:utf8 collation_connection:utf8_general_ci DatabaseCollation:latin1_swedish_ci 1rowinset(0.01sec)
mysql>SELECT*FROMINFORMATION_SCHEMA.TRIGGERSWHERETRIGGER_SCHEMA='test'ANDTRIGGER_NAME='ttlsa_users_au'\G
***************************1.row*************************** TRIGGER_CATALOG:def TRIGGER_SCHEMA:test TRIGGER_NAME:ttlsa_users_au EVENT_MANIPULATION:UPDATE EVENT_OBJECT_CATALOG:def EVENT_OBJECT_SCHEMA:test EVENT_OBJECT_TABLE:ttlsa_users ACTION_ORDER:0 ACTION_CONDITION:NULL ACTION_STATEMENT:updatettlsa_users3setuserinfo=json_object(NEW.uid,NEW.username,NEW.password)whereuid=OLD.uid 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:NO_ENGINE_SUBSTITUTION DEFINER:root@127.0.0.1 CHARACTER_SET_CLIENT:utf8 COLLATION_CONNECTION:utf8_general_ci DATABASE_COLLATION:latin1_swedish_ci 1rowinset(0.00sec)
mysql>SELECT*FROMINFORMATION_SCHEMA.TRIGGERSWHERETRIGGER_SCHEMA='test'ANDTRIGGER_NAME='ttlsa_users_au'\G
***************************1.row*************************** TRIGGER_CATALOG:def TRIGGER_SCHEMA:test TRIGGER_NAME:ttlsa_users_au EVENT_MANIPULATION:UPDATE EVENT_OBJECT_CATALOG:def EVENT_OBJECT_SCHEMA:test EVENT_OBJECT_TABLE:ttlsa_users ACTION_ORDER:0 ACTION_CONDITION:NULL ACTION_STATEMENT:updatettlsa_users3setuserinfo=json_object(NEW.uid,NEW.username,NEW.password)whereuid=OLD.uid 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:NO_ENGINE_SUBSTITUTION DEFINER:root@127.0.0.1 CHARACTER_SET_CLIENT:utf8 COLLATION_CONNECTION:utf8_general_ci DATABASE_COLLATION:latin1_swedish_ci 1rowinset(0.00sec)
3.3删除触发器
mysql>droptriggertrigger_name; mysql>droptriggertrigger_name;