0.觸發器的基本概念
觸發器是一種特殊的存儲過程，它在插入，刪除或修改特定表中的數據時觸發執行，它比數據庫本身標準的功能有更精細和更復雜的數據控制能力。
數據庫觸發器有以下的作用：
(1).安全性?？梢曰跀祿斓闹凳褂脩艟哂胁僮鲾祿斓哪撤N權利。
# 可以基于時間限制用戶的操作，例如不允許下班后和節假日修改數據庫數據。
# 可以基于數據庫中的數據限制用戶的操作，例如不允許股票的價格的升幅一次超過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_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 } 語法相關部分說明：
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
定義觸發器觸發時間?？梢栽O置為在行記錄更改之前或之后發生。
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.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; #列出數據庫的觸發器mysql> SHOW CREATE TRIGGER trigger_name; #查看創建觸發器
*************************** 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%'; #觸發器名稱匹配%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; #列出數據庫的觸發器mysql> SHOW CREATE TRIGGER trigger_name; #查看創建觸發器
*************************** 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 刪除觸發器
mysql> drop trigger trigger_name;mysql> drop trigger trigger_name;
