MySQL中觸發(fā)器的基礎(chǔ)學(xué)習(xí)教程
更新時間：2015年12月09日 11:31:07 投稿：goldensun
這篇文章主要介紹了MySQL中觸發(fā)器的基礎(chǔ)學(xué)習(xí)教程,包括對觸發(fā)器的創(chuàng)建和管理等基本知識,著力推薦!需要的朋友可以參考下
0.觸發(fā)器的基本概念
觸發(fā)器是一種特殊的存儲過程，它在插入，刪除或修改特定表中的數(shù)據(jù)時觸發(fā)執(zhí)行，它比數(shù)據(jù)庫本身標(biāo)準(zhǔn)的功能有更精細和更復(fù)雜的數(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)復(fù)雜的數(shù)據(jù)完整性規(guī)則
# 實現(xiàn)非標(biāo)準(zhǔn)的數(shù)據(jù)完整性檢查和約束。觸發(fā)器可產(chǎn)生比規(guī)則更為復(fù)雜的限制。與規(guī)則不同，觸發(fā)器可以引用列或數(shù)據(jù)庫對象。例如，觸發(fā)器可回退任何企圖吃進超過自己保證金的期貨。
# 提供可變的缺省值。
(4).實現(xiàn)復(fù)雜的非標(biāo)準(zhǔn)的數(shù)據(jù)庫相關(guān)完整性規(guī)則。觸發(fā)器可以對數(shù)據(jù)庫中相關(guān)的表進行連環(huán)更新。例如，在auths表author_code列上的刪除觸發(fā)器可導(dǎo)致相應(yīng)刪除在其它表中的與之匹配的行。
# 在修改或刪除時級聯(lián)修改或刪除其它表中的與之匹配的行。
# 在修改或刪除時把其它表中的與之匹配的行設(shè)成NULL值。
# 在修改或刪除時把其它表中的與之匹配的行級聯(lián)設(shè)成缺省值。
# 觸發(fā)器能夠拒絕或回退那些破壞相關(guān)完整性的變化，取消試圖進行數(shù)據(jù)更新的事務(wù)。當(dāng)插入一個與其主健不匹配的外部鍵時，這種觸發(fā)器會起作用。例如，可以在books.author_code 列上生成一個插入觸發(fā)器，如果新值與auths.author_code列中的某值不匹配時，插入被回退。
(5).同步實時地復(fù)制表中的數(shù)據(jù)。
(6).自動計算數(shù)據(jù)值，如果數(shù)據(jù)的值達到了一定的要求，則進行特定的處理。例如，如果公司的帳號上的資金低于5萬元則立即給財務(wù)人員發(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_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 }
語法相關(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ā)時間?？梢栽O(shè)置為在行記錄更改之前或之后發(fā)生。
1.5 trigger_event
定義觸發(fā)器觸發(fā)事件。觸發(fā)的事件有：
1.5.1
INSERT：當(dāng)一個新行插入到表中時觸發(fā)。如INSERT、LOAD DATA和REPLACE語句。
UPDATE：當(dāng)一個行數(shù)據(jù)被更改時觸發(fā)。如UPDATE語句。
DELETE：當(dāng)一個行從表中刪除時觸發(fā)。如DELETE和REPLACE語句。 注意：DROP TABLE和TRUNCATE TABLE語句不會觸發(fā)該觸發(fā)器，因為它們不是使用DELETE。同樣刪除一個分區(qū)表也不會觸發(fā)。
有一個潛在的混亂情況，如INSERT INTO ... ON DUPLICATE KEY UPDATE ... 取決于是否有重復(fù)鍵行。
不能對一個表創(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語句?？梢允侨魏魏戏ǖ恼Z句，包括復(fù)合語句(需要使用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列名來標(biāo)識，如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.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; #列出數(shù)據(jù)庫的觸發(fā)器 mysql> SHOW CREATE TRIGGER trigger_name; #查看創(chuàng)建觸發(fā)器
*************************** 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%'; #觸發(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.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; #列出數(shù)據(jù)庫的觸發(fā)器 mysql> SHOW CREATE TRIGGER trigger_name; #查看創(chuàng)建觸發(fā)器
*************************** 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 刪除觸發(fā)器
mysql> drop trigger trigger_name; mysql> drop trigger trigger_name;
相關(guān)文章
Mysql 數(shù)字類型轉(zhuǎn)換函數(shù)
Mysql 數(shù)字類型轉(zhuǎn)換函數(shù)，有此需要的朋友可以參考下用法。2009-08-08
win2008 R2 WEB環(huán)境配置之MYSQL 5.6.22安裝版安裝配置方法
這篇文章主要介紹了win2008 R2 WEB環(huán)境配置之MYSQL 5.6.22安裝版安裝配置方法,需要的朋友可以參考下2016-06-06
這篇文章主要介紹了MySQL 大表添加一列的實現(xiàn)，文中通過示例代碼介紹的非常詳細，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧2021-02-02
mysql數(shù)據(jù)庫備份及恢復(fù)命令 mysqldump,source的用法
mysql數(shù)據(jù)庫備份及恢復(fù)命令 mysqldump,source的用法，需要的朋友可以參考下。2011-02-02
這篇文章主要介紹了MySQL中的多表聯(lián)合查詢功能操作，本文結(jié)合實例代碼給大家介紹的非常詳細，對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值,需要的朋友可以參考下2023-02-02
Mysql的數(shù)據(jù)庫遷移到另一個機器上的方法詳解
今天小編就為大家分享一篇關(guān)于Mysql的數(shù)據(jù)庫遷移到另一個機器上的方法詳解，小編覺得內(nèi)容挺不錯的，現(xiàn)在分享給大家，具有很好的參考價值，需要的朋友一起跟隨小編來看看吧2019-04-04
innodb 庫的備份注意點（由phpmyadmin引起的解決方案）
新版本xampps 1.8.5 X64 集成了phpmyadmin最新版本(4.1.0), 可每次初始化安裝程序后, 進入phpmyadmin管理工具, 就會發(fā)現(xiàn)phpmyadmin高級功能失效, 一直在提示數(shù)據(jù)表不存在, 經(jīng)過再三的分析, 終于找到原因2013-12-12
這篇文章主要給大家介紹了關(guān)于MySQL組合索引與最左匹配原則的相關(guān)資料，文中通過示例代碼介紹的非常詳細，對大家學(xué)習(xí)或者使用Mysql具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起學(xué)習(xí)學(xué)習(xí)吧2019-03-03
利用MySQL函數(shù)實現(xiàn)判斷視頻擴展名的代碼
MySQL擁有強大的自定義函數(shù)功能，如下，我寫了一個用MySQL函數(shù) 判斷視頻地址是否可以手機端播放2012-02-02
這篇文章主要介紹了Mysql?exists用法的的相關(guān)資料，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下2020-12-12