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 }] trigger trigger_name trigger_time trigger_event on tbl_name for each row trigger_body trigger_time: { before | after } trigger_event: { insert | update | delete } create [definer = { user | current_user }] trigger trigger_name trigger_time trigger_event on tbl_name for each row trigger_body trigger_time: { before | after } trigger_event: { insert | update | delete }
语法相关部分说明:
1.1 授权与回收
创建触发器需要有create trigger权限:
grant create trigger on `database_naem`.`table_name` to `user_name`@`ip_address`; grant create trigger on `database_naem`.`table_name` to `user_name`@`ip_address`;
权限收回:
revoke create trigger on `database_naem`.`table_name` from `user_name`@`ip_address`; revoke create trigger on `database_naem`.`table_name` from `user_name`@`ip_address`;
1.2 trigger_name
必须给触发器命令,最多64个字符,建议用表的名字_触发器类型的缩写方法命名。如ttlsa_posts_bi(表ttlsa_posts,触发器发生在insert之前before)
1.3 definer子句
在激活触发器时,检查访问权限,确保触发器安全使用。
1.4 trigger_time
定义触发器触发时间。可以设置为在行记录更改之前或之后发生。
1.5 trigger_event
定义触发器触发事件。触发的事件有:
1.5.1
insert:当一个新行插入到表中时触发。如insert、load data和replace语句。
update:当一个行数据被更改时触发。如update语句。
delete:当一个行从表中删除时触发。如delete和replace语句。 注意:drop table和truncate table语句不会触发该触发器,因为它们不是使用delete。同样删除一个分区表也不会触发。
有一个潜在的混乱情况,如insert into ... on duplicate key update ... 取决于是否有重复键行。
不能对一个表创建具有相同的触发事件和触发时间的多个触发器。如对于一个表不能创建两个before update触发器,但是,可以创建一个before update和一个before insert或一个before update和一个after update触发器。
1.6 for each row子句
定义触发执行间隔。for each row子句定义触发器每隔一行执行一次动作,而不是对整个表执行一次。
1.7 trigger_body子句
包含要触发执行的sql语句。可以是任何合法的语句,包括复合语句(需要使用begin ... end结构),流控制语句(if、case、while、loop、for、repeat、leave、iterate),变量声明(declare)以及指派(set),异常处理声明,允许条件声明,但是这里的语句受的限制和函数的一样。
1.7.1 old与new
在触发器的sql语句中,可以关联表中的任何列,通过使用old和new列名来标识,如old.col_name、new.col_name。old.col_name关联现有的行的一列在被更新或删除前的值。new.col_name关联一个新行的插入或更新现有的行的一列的值。
对于insert语句,只有new是合法的。否则会报错:error 1363 (hy000): there is no old row in on insert trigger
对于delete语句,只有old是合法的。否则会报错:error 1363 (hy000): there is no new row in on delete trigger
对于update语句,new和old可以同时使用。
2. 实例
2.1 创建表
使用在《mysqludf_json将关系数据以json编码》一文中创建的表。后续会将用户表迁移到nosql数据库上的。
mysql> create table `ttlsa_users` ( -> `uid` int(11) unsigned, -> `username` varchar(40) not null, -> `password` varchar(40) not null, -> `createtime` timestamp not null default current_timestamp on update current_timestamp, -> primary key (`uid`) -> ); mysql> create table `ttlsa_users` ( -> `uid` int(11) unsigned, -> `username` varchar(40) not null, -> `password` varchar(40) not null, -> `createtime` timestamp not null default current_timestamp on update current_timestamp, -> primary key (`uid`) -> );
创建另外一张表来存放触发器动作数据。
mysql> create table `ttlsa_users3` ( -> `uid` int(11) unsigned, -> `userinfo` varchar(200), -> ); mysql> create table `ttlsa_users3` ( -> `uid` int(11) unsigned, -> `userinfo` varchar(200), -> );
2.2 创建触发器
mysql> delimiter // mysql> create trigger ttlsa_users_ai -> after insert on ttlsa_users -> for each row -> insert into ttlsa_users3 (uid, userinfo) values(uid, json_object(new.uid, new.username, new.password)); -> // mysql> create trigger ttlsa_users_au -> after update on ttlsa_users -> for each row -> update ttlsa_users3 set userinfo=json_object(new.uid, new.username, new.password) where uid=old.uid; -> // mysql> delimiter // mysql> create trigger ttlsa_users_ai -> after insert on ttlsa_users -> for each row -> insert into ttlsa_users3 (uid, userinfo) values(uid, json_object(new.uid, new.username, new.password)); -> // mysql> create trigger ttlsa_users_au -> after update on ttlsa_users -> for each row -> update ttlsa_users3 set userinfo=json_object(new.uid, new.username, new.password) where uid=old.uid; -> //
2.3 测试
mysql> insert into ttlsa_users values (890,'xuhh',md5('abc'),null,'test trigger')//
query ok, 1 row affected (0.01 sec)
mysql> select * from ttlsa_users//
+-----+-------------+----------------------------------+---------------------+------------------------------------+ | uid | username | password | createtime | json_data | +-----+-------------+----------------------------------+---------------------+------------------------------------+ | 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" | | 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh | | 890 | xuhh | 900150983cd24fb0d6963f7d28e17f72 | 2013-08-14 16:40:49 | test trigger | +-----+-------------+----------------------------------+---------------------+------------------------------------+ 3 rows in set (0.00 sec)
mysql> select * from ttlsa_users3//
+-----------------------------------------------------------------------------+------+ | userinfo | uid | +-----------------------------------------------------------------------------+------+ | {"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"} | 890 | +-----------------------------------------------------------------------------+------+ 2 rows in set (0.00 sec)
mysql> update ttlsa_users set password='test_update' where uid=890//
query ok, 1 row affected (0.00 sec) rows matched: 1 changed: 1 warnings: 0
mysql> select * from ttlsa_users//
+-----+-------------+----------------------------------+---------------------+------------------------------------+ | uid | username | password | createtime | json_data | +-----+-------------+----------------------------------+---------------------+------------------------------------+ | 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" | | 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh | | 890 | xuhh | test_update | 2013-08-14 16:41:33 | test trigger | +-----+-------------+----------------------------------+---------------------+------------------------------------+ 3 rows in set (0.00 sec)
mysql> select * from ttlsa_users3//
+-----------------------------------------------------------------------------+------+ | userinfo | uid | +-----------------------------------------------------------------------------+------+ | {"uid":890,"username":"xuhh","password":"test_update"} | 890 | +-----------------------------------------------------------------------------+------+ 2 rows in set (0.00 sec)
mysql> insert into ttlsa_users values (890,'xuhh',md5('abc'),null,'test trigger')//
query ok, 1 row affected (0.01 sec)
mysql> select * from ttlsa_users//
+-----+-------------+----------------------------------+---------------------+------------------------------------+ | uid | username | password | createtime | json_data | +-----+-------------+----------------------------------+---------------------+------------------------------------+ | 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" | | 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh | | 890 | xuhh | 900150983cd24fb0d6963f7d28e17f72 | 2013-08-14 16:40:49 | test trigger | +-----+-------------+----------------------------------+---------------------+------------------------------------+ 3 rows in set (0.00 sec)
mysql> select * from ttlsa_users3//
+-----------------------------------------------------------------------------+------+ | userinfo | uid | +-----------------------------------------------------------------------------+------+ | {"uid":890,"username":"xuhh","password":"900150983cd24fb0d6963f7d28e17f72"} | 890 | +-----------------------------------------------------------------------------+------+ 2 rows in set (0.00 sec)
mysql> update ttlsa_users set password='test_update' where uid=890//
query ok, 1 row affected (0.00 sec) rows matched: 1 changed: 1 warnings: 0
mysql> select * from ttlsa_users//
+-----+-------------+----------------------------------+---------------------+------------------------------------+ | uid | username | password | createtime | json_data | +-----+-------------+----------------------------------+---------------------+------------------------------------+ | 888 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 11:27:01 | \website\ - "http://www.ttlsa.com" | | 889 | ttlsa_admin | 6a6e41c9b741f740cfa5f266b249d452 | 2013-08-10 14:08:44 | xuhh | | 890 | xuhh | test_update | 2013-08-14 16:41:33 | test trigger | +-----+-------------+----------------------------------+---------------------+------------------------------------+ 3 rows in set (0.00 sec)
mysql> select * from ttlsa_users3//
+-----------------------------------------------------------------------------+------+ | userinfo | uid | +-----------------------------------------------------------------------------+------+ | {"uid":890,"username":"xuhh","password":"test_update"} | 890 | +-----------------------------------------------------------------------------+------+ 2 rows in set (0.00 sec)
3. 管理
3.1 列出触发器
mysql> show triggers like '%ttlsa%'; 触发器名称匹配%ttlsa%
*************************** 1. row *************************** trigger: ttlsa_users_ai event: insert table: ttlsa_users statement: insert into ttlsa_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 database collation: latin1_swedish_ci *************************** 2. row *************************** trigger: ttlsa_users_au event: update table: ttlsa_users statement: update ttlsa_users3 set userinfo=json_object(new.uid, new.username, new.password) where uid=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 database collation: latin1_swedish_ci 2 rows in set (0.00 sec)
mysql> show triggers; #列出所有 mysql> show triggers from database_name; #列出数据库的触发器 mysql> show create trigger trigger_name; #查看创建触发器
*************************** 1. row *************************** trigger: ttlsa_users_ai sql_mode: no_engine_substitution sql original statement: create definer=`root`@`127.0.0.1` trigger ttlsa_users_ai after insert on ttlsa_users for each row insert into ttlsa_users3 (uid,userinfo) values(new.uid,json_object(new.uid, new.username, new.password)) character_set_client: utf8 collation_connection: utf8_general_ci database collation: latin1_swedish_ci 1 row in set (0.01 sec)
3.2 information_schema.triggers表
sql> show triggers like '%ttlsa%'; #触发器名称匹配%ttlsa%
*************************** 1. row *************************** trigger: ttlsa_users_ai event: insert table: ttlsa_users statement: insert into ttlsa_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 database collation: latin1_swedish_ci *************************** 2. row *************************** trigger: ttlsa_users_au event: update table: ttlsa_users statement: update ttlsa_users3 set userinfo=json_object(new.uid, new.username, new.password) where uid=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 database collation: latin1_swedish_ci 2 rows in set (0.00 sec)
mysql> show triggers; #列出所有 mysql> show triggers from database_name; #列出数据库的触发器 mysql> show create trigger trigger_name; #查看创建触发器
*************************** 1. row *************************** trigger: ttlsa_users_ai sql_mode: no_engine_substitution sql original statement: create definer=`root`@`127.0.0.1` trigger ttlsa_users_ai after insert on ttlsa_users for each row insert into ttlsa_users3 (uid,userinfo) values(new.uid,json_object(new.uid, new.username, new.password)) character_set_client: utf8 collation_connection: utf8_general_ci database collation: latin1_swedish_ci 1 row in set (0.01 sec)
mysql> select * from information_schema.triggers where trigger_schema='test' and trigger_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: update ttlsa_users3 set userinfo=json_object(new.uid, new.username, new.password) where uid=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 1 row in set (0.00 sec)
mysql> select * from information_schema.triggers where trigger_schema='test' and trigger_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: update ttlsa_users3 set userinfo=json_object(new.uid, new.username, new.password) where uid=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 1 row in set (0.00 sec)
3.3 删除触发器
mysql> drop trigger trigger_name; mysql> drop trigger trigger_name;