《Mysql學(xué)習(xí)MySQL中觸發(fā)器的基礎(chǔ)學(xué)習(xí)教程》要點：
本文介紹了Mysql學(xué)習(xí)MySQL中觸發(fā)器的基礎(chǔ)學(xué)習(xí)教程，希望對您有用。如果有疑問，可以聯(lián)系我們。
0.觸發(fā)器的基本概念
觸發(fā)器是一種特殊的存儲過程,它在插入,刪除或修改特定表中的數(shù)據(jù)時觸發(fā)執(zhí)行,它比數(shù)據(jù)庫本身標準的功能有更精細和更復(fù)雜的數(shù)據(jù)控制能力.MYSQL學(xué)習(xí)
數(shù)據(jù)庫觸發(fā)器有以下的作用：MYSQL學(xué)習(xí)
(1).安全性.可以基于數(shù)據(jù)庫的值使用戶具有操作數(shù)據(jù)庫的某種權(quán)利.MYSQL學(xué)習(xí)
? # 可以基于時間限制用戶的操作,例如不允許下班后和節(jié)假日修改數(shù)據(jù)庫數(shù)據(jù).MYSQL學(xué)習(xí)
? # 可以基于數(shù)據(jù)庫中的數(shù)據(jù)限制用戶的操作,例如不允許股票的價格的升幅一次超過10%.MYSQL學(xué)習(xí)
(2).審計.可以跟蹤用戶對數(shù)據(jù)庫的操作.??MYSQL學(xué)習(xí)
? # 審計用戶操作數(shù)據(jù)庫的語句.MYSQL學(xué)習(xí)
? # 把用戶對數(shù)據(jù)庫的更新寫入審計表.MYSQL學(xué)習(xí)
(3).實現(xiàn)復(fù)雜的數(shù)據(jù)完整性規(guī)則MYSQL學(xué)習(xí)
? # 實現(xiàn)非標準的數(shù)據(jù)完整性檢查和約束.觸發(fā)器可產(chǎn)生比規(guī)則更為復(fù)雜的限制.與規(guī)則不同,觸發(fā)器可以引用列或數(shù)據(jù)庫對象.例如,觸發(fā)器可回退任何企圖吃進超過自己保證金的期貨.MYSQL學(xué)習(xí)
? # 提供可變的缺省值.MYSQL學(xué)習(xí)
(4).實現(xiàn)復(fù)雜的非標準的數(shù)據(jù)庫相關(guān)完整性規(guī)則.觸發(fā)器可以對數(shù)據(jù)庫中相關(guān)的表進行連環(huán)更新.例如,在auths表author_code列上的刪除觸發(fā)器可導(dǎo)致相應(yīng)刪除在其它表中的與之匹配的行.MYSQL學(xué)習(xí)
? # 在修改或刪除時級聯(lián)修改或刪除其它表中的與之匹配的行.MYSQL學(xué)習(xí)
? # 在修改或刪除時把其它表中的與之匹配的行設(shè)成NULL值.MYSQL學(xué)習(xí)
? # 在修改或刪除時把其它表中的與之匹配的行級聯(lián)設(shè)成缺省值.MYSQL學(xué)習(xí)
? # 觸發(fā)器能夠拒絕或回退那些破壞相關(guān)完整性的變化,取消試圖進行數(shù)據(jù)更新的事務(wù).當(dāng)插入一個與其主健不匹配的外部鍵時,這種觸發(fā)器會起作用.例如,可以在books.author_code 列上生成一個插入觸發(fā)器,如果新值與auths.author_code列中的某值不匹配時,插入被回退.MYSQL學(xué)習(xí)
(5).同步實時地復(fù)制表中的數(shù)據(jù).MYSQL學(xué)習(xí)
(6).自動計算數(shù)據(jù)值,如果數(shù)據(jù)的值達到了一定的要求,則進行特定的處理.例如,如果公司的帳號上的資金低于5萬元則立即給財務(wù)人員發(fā)送警告數(shù)據(jù).MYSQL學(xué)習(xí)
1. 創(chuàng)建觸發(fā)器語法MYSQL學(xué)習(xí)
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)限：MYSQL學(xué)習(xí)
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)限收回：MYSQL學(xué)習(xí)
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ā)時間.可以設(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語句.可以是任何合法的語句,包括復(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列名來標識,如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學(xué)習(xí)
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學(xué)習(xí)
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學(xué)習(xí)
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學(xué)習(xí)
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學(xué)習(xí)
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學(xué)習(xí)
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學(xué)習(xí)
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學(xué)習(xí)
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學(xué)習(xí)
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表MYSQL學(xué)習(xí)
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學(xué)習(xí)
mysql> drop trigger trigger_name; mysql> drop trigger trigger_name;
?MYSQL學(xué)習(xí)
轉(zhuǎn)載請注明本頁網(wǎng)址：
http://www.snjht.com/jiaocheng/5961.html
