MySql觸發器使用講解

標籤：mysql   觸發器   

      林炳文Evankaka原創作品。轉載請註明出處http://blog.csdn.net/evankaka

      觸發器（TRIGGER）是由事件來觸發某個操作。這些事件包括INSERT語句、UPDATE語句和DELETE語句。當資料庫系統執行這些事件時，就會啟用觸發器執行相應的操作。MySQL從5.0.2版本開始支援觸發器。在本文中將講解的內容包括：
觸發器的含義和作用
如何建立觸發器
如何查看觸發器
如何刪除觸發器

       觸發器是由INSERT、UPDATE和DELETE等事件來觸發某種特定操作。滿足觸發器的觸發條件時，資料庫系統就會執行觸發器中定義的程式語句。這樣做可以保證某些操作之間的一致性。例如，當學生表中增加了一個學生的資訊時，學生的總數就必須同時改變。可以在這裡建立一個觸發器，每次增加一個學生的記錄，就執行一次計算學生總數的操作。這樣就可以保證每次增加學生的記錄後，學生總數是與記錄數是一致的。觸發器觸發的執行語句可能只有一個，也可能有多個。本節將詳細講解建立觸發器的方法。

一、文法

建立只有一個執行語句的觸發器

MySQL中，建立只有一個執行語句的觸發器的基本形式如下：

CREATE TRIGGER 觸發器名 BEFORE | AFTER 觸發事件ON 表名 FOR EACH ROW 執行語句

建立有多個執行語句的觸發器

MySQL中，觸發器觸發的執行語句可能有多個。建立有多個執行語句的觸發器的基本形式如下：

CREATE TRIGGER 觸發器名 BEFORE | AFTER 觸發事件ON 表名 FOR EACH ROWBEGIN執行語句列表END

下面來具體說說文法

1、命名規則

CREATE TRIGGER <觸發器名稱> <--{ BEFORE | AFTER }{ INSERT | UPDATE | DELETE }ON <表名稱>FOR EACH ROWBEGIN<觸發器SQL語句>END

觸發器必須有名字，最多64個字元，可能後面會附有分隔字元.它和MySQL中其他對象的命名方式基本相象.
2、觸發時間： BEFORE | AFTER
觸發器有執行的時間設定：INSERT | UPDATE | DELETE
3、觸發事件前後
同樣也能設定觸發的事件：它們可以在執行insert、update或delete的過程中觸發。
4.、表
觸發器是屬於某一個表的:當在這個表上執行插入、
更新或刪除操作的時候就導致觸發器的啟用.
我們不能給同一張表的同一個事件安排兩個觸發器。
5.、（ 步長）觸發間隔
觸發器的執行間隔：FOR EACH ROW子句通知觸發器
每隔一行執行一次動作，而不是對整個表執行一次。
6、 語句
觸發器包含所要觸發的SQL語句：這裡的語句可以是任何合法的語句，
包括複合陳述式，但是這裡的語句受的限制和函數的一樣。
Privileges許可權
你必須擁有相當大的許可權才能建立觸發器（CREATE TRIGGER）。如果你已經是Root使用者，那麼就足夠了。這跟SQL的標準有所不同。


二、關於舊的和新建立的列的標識     在觸發器的SQL語句中，你可以關聯表中的任意列。但你不能僅使用列的名稱去標識，那會使系統混淆，因為那裡可能會有列的新名（這可能正是你要修改的，你的動作可能正是要修改列名），還有列的舊名存在。因此你必須用這樣的文法來標識： "NEW . column_name"或者"OLD . column_name".這樣在技術上處理（NEW | OLD . column_name）新和舊的列名屬於建立了過渡變數（"transition variables"）。

      對於INSERT語句,只有NEW是合法的；對於DELETE語句，只有OLD才合法；而UPDATE語句可以在和NEW以及OLD同時使用。

三、使用範例

先建立兩個表、一個訂單表，一個訂單時間表

CREATE TABLE T_ORDER(ORDER_NUM INT PRIMARY KEY,ORDER_NAME CHAR(10) NOT NULL);CREATE TABLE T_ORDER_TIME(ORDER_NUM INT PRIMARY KEY,ORDER_TIME TIMESTAMP NOT NULL,)

1、建立插入時的觸發器

CREATE TRIGGER TRI_INSERTAFTER INSERT ON T_ORDER FOR EACH ROWBEGININSERT INTO learning.t_order_time(ORDER_NUM,ORDER_TIME) VALUES(NEW.ORDER_NUM,NOW());END

然後訂單表中插入一條資料

INSERT INTO T_ORDER(ORDER_NUM,ORDER_NAME) VALUES(1,‘電腦‘);SELECT * FROM t_order;SELECT * FROM t_order_time;

這是訂單表中插入的資料：

這是訂單時間表中自動插入的資料：

2、更新時插入資料

//更新建立觸發器CREATE TRIGGER TRI_UPDATABEFORE UPDATE ON T_ORDER FOR EACH ROWBEGINUPDATE  learning.t_order_time SET ORDER_TIME=NOW() WHERE OLD.ORDER_NUM=ORDER_NUM;ENDUPDATE T_ORDER  SET ORDER_NAME=‘餅乾餅‘ WHERE ORDER_NUM=1;SELECT * FROM t_order;SELECT * FROM t_order_time;

可以看到，時間已經更新

3、建立刪除觸發器

//建立刪除觸發器CREATE TRIGGER TRI_DELETEAFTER DELETE ON T_ORDER FOR EACH ROWBEGINDELETE FROM  learning.t_order_time  WHERE OLD.ORDER_NUM=ORDER_NUM;ENDDELETE FROM  learning.t_order  WHERE ORDER_NUM=1;SELECT * FROM t_order;SELECT * FROM t_order_time;

可以看到，資料庫中都沒有資料了。

4、查看觸發器

查看觸發器是指查看資料庫中已存在的觸發器的定義、狀態和文法等資訊。查看觸發器的方法包括SHOW TRIGGERS語句和查詢information_schema資料庫下的triggers表等。本節將詳細講解查看觸發器的方法。

MySQL中，可以執行SHOW TRIGGERS語句來查看觸發器的基本資料。其基本形式如下：

SHOW TRIGGERS ;

MySQL中，所有觸發器的定義都存在information_schema資料庫下的triggers表中。查詢triggers表，可以查看到資料庫中所有觸發器的詳細資料。查詢的語句如下：

SELECT *  FROM  information_schema.triggers;

5、刪除觸發器

DROP TRIGGER 觸發器名

6、觸發器 與預存程序
觸發程式不能調用將資料返回用戶端的儲存程式，也不能使用採用CALL語句的動態SQL
（允許儲存程式通過參數將資料返回觸發程式）。
而預存程序 可以接受參數，將結果範圍給應用程式

著作權聲明：本文為博主林炳文Evankaka原創文章，未經博主允許不得轉載。

MySql觸發器使用講解