MySQL中觸發器的基礎學習教程_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_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
定義觸發器觸發時間。可以設定為在行記錄更改之前或之後發生。
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;