十一、MySQL觸發器

標籤：mysql、觸發器

  MySQL的觸發器和預存程序一樣，都是嵌入到MySQL的一段程式。觸發器是由時間來觸發某個操作，這些時間包括INSERT、UODATE和DELETE語句。如果定義了觸發程式，當資料庫執行這些語句的時候就會觸發執行相應的操作，觸發程式是與表有關的命名資料庫物件，當表上出現特定事件時，當啟用該對象。

11.1、建立觸發器

 觸發器是一個特殊的預存程序，不同的是，執行預存程序要使用CALL語句來調用，而觸發器的執行不需要使用CALL語句來調用，也不需要手工啟動，只要當預定義的時間發生的時候，就會被MySQL自動調用。

 建立一個觸發器的文法為：

CREATE TRIGGER trigger_name trigger_time trigger_event    ON tbl_name FOR EACH ROW trigger_body

trigger_name：觸發器名稱，使用者自行指定 trigger_time：觸發時機，可以指定為before或after  trigger_event：標識觸發事件 tbl_name：標識觸發器的表名 trigger_body：觸發器執行語句

建立一個單執行語句的觸發器：

mysql> CREATE TABLE account (acc_num INT,amount DECIMAL(10,2));Query OK, 0 rows affected (0.06 sec)mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account    -> FOR EACH ROW SET @sum = @sum +NEW.amount;Query OK, 0 rows affected (0.02 sec)mysql> SET @sum=0;Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO account VALUES (1,1.00),(2,2.00);Query OK, 2 rows affected (0.02 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> SELECT @sum;+------+| @sum |+------+| 3.00 |+------+1 row in set (0.00 sec)

建立多個執行語句的觸發器的文法：

CREATE TRIGGER trigger_name trigger_time trigger_eventON tbl_name FOR EACH ROW BEGIN  trigger_stmtEND

11.2、查看觸發器

 查看觸發器是指查看資料庫中已存在的觸發器的定義、狀態和觸發資訊等可以通過SHOW TRUGGERS和在triggers表中查看觸發器資訊。

mysql> CREATE TABLE myevent    -> (    -> id int(11) DEFAULT NULL,    -> evt_name char(20) DEFAULT NULL    -> );Query OK, 0 rows affected (0.03 sec)mysql> CREATE TRIGGER trig_update AFTER UPDATE ON account    -> FOR EACH ROW INSERT INTO myevent VALUES (1,‘AFTER UPDATE‘);Query OK, 0 rows affected (0.05 sec)mysql> SHOW TRIGGERS \G*************************** 1. row ***************************             Trigger: ins_sum               Event: INSERT               Table: account           Statement: SET @sum = @sum +NEW.amount              Timing: BEFORE             Created: NULL            sql_mode:              Definer: [email protected]character_set_client: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci*************************** 2. row ***************************             Trigger: trig_update               Event: UPDATE               Table: account           Statement: INSERT INTO myevent VALUES (1,‘AFTER UPDATE‘)              Timing: AFTER             Created: NULL            sql_mode:              Definer: [email protected]character_set_client: utf8collation_connection: utf8_general_ci  Database Collation: utf8_general_ci2 rows in set (0.00 sec)

  在MySQL中所有觸發器的定義都存在INFORMATION_SCHEMA資料庫的TRIGGERS表中，可以通過SELECT 來查看，其文法格式為：

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition;

mysql> SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME=‘trig_update‘ \G*************************** 1. row ***************************           TRIGGER_CATALOG: def            TRIGGER_SCHEMA: test              TRIGGER_NAME: trig_update        EVENT_MANIPULATION: UPDATE      EVENT_OBJECT_CATALOG: def       EVENT_OBJECT_SCHEMA: test        EVENT_OBJECT_TABLE: account              ACTION_ORDER: 0          ACTION_CONDITION: NULL          ACTION_STATEMENT: INSERT INTO myevent VALUES (1,‘AFTER UPDATE‘)        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:                    DEFINER: [email protected]      CHARACTER_SET_CLIENT: utf8      COLLATION_CONNECTION: utf8_general_ci        DATABASE_COLLATION: utf8_general_ci1 row in set (0.00 sec)

11.3、觸發器的使用

  觸發程式是與表有關的命名資料庫物件，當表上出現特定時間時，將啟用該對象。

建立一個在account表插入資料之後，更新myevent資料表的觸發器。

mysql> CREATE TRIGGER trig_inster AFTER INSERT ON account     -> FOR EACH ROW INSERT INTO myevent VALUES (2,‘AFTER INSERT‘); Query OK, 0 rows affected (0.02 sec)mysql> INSERT INTO account VALUES (1,1.00),(2,2.00);Query OK, 2 rows affected (0.01 sec)Records: 2  Duplicates: 0  Warnings: 0mysql> SELECT * FROM myevent;+------+--------------+| id   | evt_name     |+------+--------------+|    2 | AFTER INSERT ||    2 | AFTER INSERT |+------+--------------+2 rows in set (0.00 sec)

11.4、刪除觸發器

  使用DROP TRIGGER語句可以刪除MySQL中已經存在的觸發器，其文法格式為：

DROP TRIGGER [schema_name.]trigger_name

刪除一個觸發器

mysql> DROP TRIGGER test.ins_sum;Query OK, 0 rows affected (0.02 sec)

本文出自 “隨風而飄” 部落格，請務必保留此出處http://yinsuifeng.blog.51cto.com/10173491/1953884

十一、MySQL觸發器