0.觸發(fā)器的基本概念
觸發(fā)器是一種特殊的存儲過程，它在插入，刪除或修改特定表中的數(shù)據(jù)時觸發(fā)執(zhí)行，它比數(shù)據(jù)庫本身標準的功能有更精細和更復雜的數(shù)據(jù)控制能力。
數(shù)據(jù)庫觸發(fā)器有以下的作用：
(1).安全性。可以基于數(shù)據(jù)庫的值使用戶具有操作數(shù)據(jù)庫的某種權(quán)利。
# 可以基于時間限制用戶的操作，例如不允許下班后和節(jié)假日修改數(shù)據(jù)庫數(shù)據(jù)。
# 可以基于數(shù)據(jù)庫中的數(shù)據(jù)限制用戶的操作，例如不允許股票的價格的升幅一次超過10%。
(2).審計。可以跟蹤用戶對數(shù)據(jù)庫的操作。
# 審計用戶操作數(shù)據(jù)庫的語句。
# 把用戶對數(shù)據(jù)庫的更新寫入審計表。
(3).實現(xiàn)復雜的數(shù)據(jù)完整性規(guī)則
# 實現(xiàn)非標準的數(shù)據(jù)完整性檢查和約束。觸發(fā)器可產(chǎn)生比規(guī)則更為復雜的限制。與規(guī)則不同，觸發(fā)器可以引用列或數(shù)據(jù)庫對象。例如，觸發(fā)器可回退任何企圖吃進超過自己保證金的期貨。
# 提供可變的缺省值。
(4).實現(xiàn)復雜的非標準的數(shù)據(jù)庫相關(guān)完整性規(guī)則。觸發(fā)器可以對數(shù)據(jù)庫中相關(guān)的表進行連環(huán)更新。例如，在auths表author_code列上的刪除觸發(fā)器可導致相應刪除在其它表中的與之匹配的行。
# 在修改或刪除時級聯(lián)修改或刪除其它表中的與之匹配的行。
# 在修改或刪除時把其它表中的與之匹配的行設成NULL值。
# 在修改或刪除時把其它表中的與之匹配的行級聯(lián)設成缺省值。
# 觸發(fā)器能夠拒絕或回退那些破壞相關(guān)完整性的變化，取消試圖進行數(shù)據(jù)更新的事務。當插入一個與其主健不匹配的外部鍵時，這種觸發(fā)器會起作用。例如，可以在books.author_code 列上生成一個插入觸發(fā)器，如果新值與auths.author_code列中的某值不匹配時，插入被回退。
(5).同步實時地復制表中的數(shù)據(jù)。
(6).自動計算數(shù)據(jù)值，如果數(shù)據(jù)的值達到了一定的要求，則進行特定的處理。例如，如果公司的帳號上的資金低于5萬元則立即給財務人員發(fā)送警告數(shù)據(jù)。
1. 創(chuàng)建觸發(fā)器語法
CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_bodytrigger_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 } 語法相關(guān)部分說明：
1.1 授權(quán)與回收
創(chuàng)建觸發(fā)器需要有CREATE TRIGGER權(quán)限：
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`;
權(quán)限收回：
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
必須給觸發(fā)器命令，最多64個字符，建議用表的名字_觸發(fā)器類型的縮寫方法命名。如ttlsa_posts_bi(表ttlsa_posts，觸發(fā)器發(fā)生在insert之前before)
1.3 DEFINER子句
在激活觸發(fā)器時，檢查訪問權(quán)限，確保觸發(fā)器安全使用。
1.4 trigger_time
定義觸發(fā)器觸發(fā)時間。可以設置為在行記錄更改之前或之后發(fā)生。
1.5 trigger_event
定義觸發(fā)器觸發(fā)事件。觸發(fā)的事件有：
1.5.1
INSERT：當一個新行插入到表中時觸發(fā)。如INSERT、LOAD DATA和REPLACE語句。
UPDATE：當一個行數(shù)據(jù)被更改時觸發(fā)。如UPDATE語句。
DELETE：當一個行從表中刪除時觸發(fā)。如DELETE和REPLACE語句。 注意：DROP TABLE和TRUNCATE TABLE語句不會觸發(fā)該觸發(fā)器，因為它們不是使用DELETE。同樣刪除一個分區(qū)表也不會觸發(fā)。
有一個潛在的混亂情況，如INSERT INTO ... ON DUPLICATE KEY UPDATE ... 取決于是否有重復鍵行。
不能對一個表創(chuàng)建具有相同的觸發(fā)事件和觸發(fā)時間的多個觸發(fā)器。如對于一個表不能創(chuàng)建兩個BEFORE UPDATE觸發(fā)器，但是，可以創(chuàng)建一個BEFORE UPDATE和一個BEFORE INSERT或一個BEFORE UPDATE和一個AFTER UPDATE觸發(fā)器。
1.6 FOR EACH ROW子句
定義觸發(fā)執(zhí)行間隔。FOR EACH ROW子句定義觸發(fā)器每隔一行執(zhí)行一次動作，而不是對整個表執(zhí)行一次。
1.7 trigger_body子句
包含要觸發(fā)執(zhí)行的SQL語句。可以是任何合法的語句，包括復合語句(需要使用BEGIN ... END結(jié)構(gòu))，流控制語句(if、case、while、loop、for、repeat、leave、iterate)，變量聲明(declare)以及指派(set)，異常處理聲明，允許條件聲明，但是這里的語句受的限制和函數(shù)的一樣。
1.7.1 OLD與NEW
在觸發(fā)器的SQL語句中，可以關(guān)聯(lián)表中的任何列，通過使用OLD和NEW列名來標識，如OLD.col_name、NEW.col_name。OLD.col_name關(guān)聯(lián)現(xiàn)有的行的一列在被更新或刪除前的值。NEW.col_name關(guān)聯(lián)一個新行的插入或更新現(xiàn)有的行的一列的值。
對于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 創(chuàng)建表
使用在《mysqludf_json將關(guān)系數(shù)據(jù)以JSON編碼》一文中創(chuàng)建的表。后續(xù)會將用戶表遷移到nosql數(shù)據(jù)庫上的。
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`) -> );
創(chuàng)建另外一張表來存放觸發(fā)器動作數(shù)據(jù)。
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 創(chuàng)建觸發(fā)器
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 列出觸發(fā)器
mysql> SHOW TRIGGERS like '%ttlsa%'; 觸發(fā)器名稱匹配%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.1character_set_client: utf8collation_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.1character_set_client: utf8collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci2 rows in set (0.00 sec)
mysql> SHOW TRIGGERS; #列出所有mysql> SHOW TRIGGERS from database_name; #列出數(shù)據(jù)庫的觸發(fā)器mysql> SHOW CREATE TRIGGER trigger_name; #查看創(chuàng)建觸發(fā)器
*************************** 1. row *************************** Trigger: ttlsa_users_ai sql_mode: NO_ENGINE_SUBSTITUTIONSQL 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_ci1 row in set (0.01 sec)
3.2 INFORMATION_SCHEMA.TRIGGERS表
sql> SHOW TRIGGERS like '%ttlsa%'; #觸發(fā)器名稱匹配%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.1character_set_client: utf8collation_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.1character_set_client: utf8collation_connection: utf8_general_ci Database Collation: latin1_swedish_ci2 rows in set (0.00 sec)
mysql> SHOW TRIGGERS; #列出所有mysql> SHOW TRIGGERS from database_name; #列出數(shù)據(jù)庫的觸發(fā)器mysql> SHOW CREATE TRIGGER trigger_name; #查看創(chuàng)建觸發(fā)器
*************************** 1. row *************************** Trigger: ttlsa_users_ai sql_mode: NO_ENGINE_SUBSTITUTIONSQL 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_ci1 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: AFTERACTION_REFERENCE_OLD_TABLE: NULLACTION_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_ci1 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: AFTERACTION_REFERENCE_OLD_TABLE: NULLACTION_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_ci1 row in set (0.00 sec)
3.3 刪除觸發(fā)器
mysql> drop trigger trigger_name;mysql> drop trigger trigger_name;
