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;