程式師世界 >> 數據庫知識 >> MYSQL數據庫 >> 關於MYSQL數據庫 >> MySQL中觸發器的基礎學習教程

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_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 }

語法相關部分說明：
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: [email protected]
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: [email protected]
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; #列出數據庫的觸發器
mysql> SHOW CREATE TRIGGER trigger_name;  #查看創建觸發器

*************************** 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%'; #觸發器名稱匹配%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: [email protected]
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: [email protected]
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; #列出數據庫的觸發器
mysql> SHOW CREATE TRIGGER trigger_name;  #查看創建觸發器

*************************** 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: [email protected]
   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: [email protected]
   CHARACTER_SET_CLIENT: utf8
   COLLATION_CONNECTION: utf8_general_ci
    DATABASE_COLLATION: latin1_swedish_ci
1 row in set (0.00 sec)

3.3 刪除觸發器

mysql> drop trigger trigger_name;
mysql> drop trigger trigger_name;

關於MYSQL數據庫

Mysql入門系列：MYSQL服務器內部安全性-安全數據目錄訪問

　　本章主要討論作為MySQL管理員的您，在維護My

MYSQL 淺談MyISAM 存儲引擎

思維導圖介紹

MySQL數據庫技術(18)

　　3.8 解決方案隨筆 &n

在MySQL中使用通配符時應該注意的問題

現象：有一個表 action_conf，數據如下：如果想獲取

MySQL無法啟動、無法停止解決方法(安全設置後容易出現)

最近在Windows 2003上的MySQL出現過多次正常運

MYSQL日志文件維護

　　在MySQL服務器啟動時，它檢查其命令行的操作，來查看

閱讀排行榜

忘記了 MySQL 的 root 密碼_MySQL教程詳解MySQL數據庫之更新語句 MySQL5創建存儲過程如何解決Mysql主機 host_name被屏蔽 MySQL性能參數詳解之Skip-External-Locking參數介紹擁有5星評級數據庫表結構如何才能更高效的使用? MySQL安全性指南(2) MYSQL的MySQL Show命令讓SELECT 查詢結果額外增加自動遞增序號 MySQL如何清空慢查詢文件如何去除數據表中的重復數據

熱門圖文

mysql 數據庫基礎筆記深入解析mysql.sock不見的問題 DB2中的數據移動（一） oracle11g新特點——AdaptiveCursorSharing(ACS) 五分鐘了解什麼是Oracle數據庫 MySQL關鍵性能監控(QPS/TPS)，qpstps 如何修改Syabse服務器端的默認字符集修改Linux下mysql 5.0.xx默認連接數

欄目導航

MYSQL入門知識 SQL基礎知識關於MYSQL數據庫 Mariadb數據庫 MySQL綜合教程