mysql觸發器的作用及文法

標籤：style   color   io   使用   ar   for   資料   sp   div   

 

觸發器是一種特殊的預存程序，它在插入，刪除或改動特定表中的資料時觸發運行，它比資料庫本身標準的功能有更精細和更複雜的資料控制能力。

資料庫觸發器有下面的作用：

1.安全性。能夠基於資料庫的值使使用者具有操作資料庫的某種權利。

  # 能夠基於時間限制使用者的操作，比如不同意下班後和節假日改動資料庫資料。

  # 能夠基於資料庫中的資料限制使用者的操作，比如不同意股票的價格的升幅一次超過10%。

2.審計。能夠跟蹤使用者對資料庫的操作。   

  # 審計使用者操作資料庫的語句。

  # 把使用者對資料庫的更新寫入審計表。

3.實現複雜的資料完整性規則

  # 實現非標準的資料完整性檢查和約束。觸發器可產生比規則更為複雜的限制。與規則不同，觸發器能夠引用列或資料庫物件。比如，觸發器可回退不論什麼企圖吃進超過自己保證金的期貨。

  # 提供可變的預設值。

4.實現複雜的非標準的資料庫相關完整性規則。觸發器能夠對資料庫中相關的表進行連環更新。比如，在auths表author_code列上的刪除觸發器可導致對應刪除在其他表中的與之匹配的行。

  # 在改動或刪除時級聯改動或刪除其他表中的與之匹配的行。

  # 在改動或刪除時把其他表中的與之匹配的行設成NULL值。

  # 在改動或刪除時把其他表中的與之匹配的行級聯設成預設值。

  # 觸發器可以拒絕或回退那些破壞相關完整性的變化，取消試圖進行資料更新的事務。當插入一個與其主健不匹配的外部鍵時，這樣的觸發器會起作用。比如，可以在books.author_code 列上產生一個插入觸發器，假設新值與auths.author_code列中的某值不匹配時，插入被回退。

5.同步即時地複製表中的資料。

6.自己主動計算資料值，假設資料的值達到了一定的要求，則進行特定的處理。比如，假設公司的帳號上的資金低於5萬元則馬上給財務人員發送警告資料。

MySQL觸發器文法具體解釋.
㈠CREATE TRIGGER文法
CREATE TRIGGER trigger_name trigger_time trigger_event
    ON tbl_name FOR EACH ROW trigger_stmt
觸發程式是與表有關的命名資料庫物件，當表上出現特定事件時，將啟用該對象。
觸發程式與命名為tbl_name的表相關。tbl_name必須引用永久性表。不能將觸發程式與
TEMPORARY表或視圖關聯起來。
trigger_time是觸發程式的動作時間。它能夠是BEFORE或AFTER，以指明觸發程式是在啟用它的語句之前或之後觸發。
trigger_event指明了啟用觸發程式的語句的類型。trigger_event能夠是下述值之中的一個：
·         INSERT：將新行插入表時啟用觸發程式，比如，通過INSERT、LOAD DATA和REPLACE
語句。
·         UPDATE：更改某一行時啟用觸發程式，比如，通過UPDATE語句。
·         DELETE：從表中刪除某一行時啟用觸發程式，比如，通過DELETE和REPLACE語句。
請注意，trigger_event與以表操作方式啟用觸發程式的SQL語句並不非常類似，這點非常重要。比如，
關於INSERT的BEFORE觸發程式不僅能被INSERT語句啟用，也能被LOAD DATA語句啟用。
可能會造成混淆的範例之中的一個是INSERT INTO .. ON DUPLICATE UPDATE ...文法：BEFORE INSERT
觸發程式對於每一行將啟用，後跟AFTER INSERT觸發程式，或BEFORE UPDATE和AFTER
UPDATE觸發程式，詳細情況取決於行上是否有反覆鍵。
對於具有同樣觸發程式動作時間和事件的給定表，不能有兩個觸發程式。比如，對於某一表，不
能有兩個BEFORE UPDATE觸發程式。但能夠有1個BEFORE UPDATE觸發程式和1個BEFORE
INSERT觸發程式，或1個BEFORE UPDATE觸發程式和1個AFTER UPDATE觸發程式。
trigger_stmt是當觸發程式啟用時啟動並執行語句。假設你打算運行多個語句，可使用BEGIN ... END複
合語句結構。這樣，就能使用儲存子程式中同意的同樣語句

㈡DROP TRIGGER文法
DROP TRIGGER [schema_name.]trigger_name
捨棄觸發程式。方案名稱（schema_name）是可選的。假設省略了schema（方案），將從當前方
案中捨棄觸發程式。
凝視：從MySQL 5.0.10之前的MySQL版本號碼升級到5.0.10或更高版本號碼時（包含全部的MySQL 5.1版
本），必須在升級之前捨棄全部的觸發程式，並在隨後又一次建立它們，否則，在升級之後DROP
TRIGGER不工作。
DROP TRIGGER語句須要SUPER許可權。

㈢使用觸發程式
在本節中，介紹了在MySQL 5.1中使用觸發程式的方法，並介紹了在使用觸發程式方面的限制。
觸發程式是與表有關的命名資料庫物件，當表上出現特定事件時，將啟用該對象。在某些觸發程
序的使用方法中，可用於檢查插入到表中的值，或對更新涉及的值進行計算。
觸發程式與表相關，當對錶運行INSERT、DELETE或UPDATE語句時，將啟用觸發程式。能夠將
觸發程式設定為在運行語句之前或之後啟用。比如，能夠在從表中刪除每一行之前，或在更新了
每一行後啟用觸發程式。
要想建立觸發程式或捨棄觸發程式，可使用CREATE TRIGGER或DROP TRIGGER語句

·         觸發程式不能調用將資料返回client的儲存程式，也不能使用採用CALL語句的動態SQL
（同意儲存程式通過參數將資料返回觸發程式）。
·         觸發程式不能使用以顯式或隱式方式開始或結束事務的語句，如START TRANSACTION、
COMMIT或ROLLBACK。
使用OLD和NEWkeyword，可以訪問受觸發程式影響的行中的列（OLD和NEW不區分大寫和小寫）。
在INSERT觸發程式中，僅能使用NEW.col_name，沒有舊行。在DELETE觸發程式中，僅能使用
OLD.col_name，沒有新行。在UPDATE觸發程式中，能夠使用OLD.col_name來引用更新前的某一
行的列，也能使用NEW.col_name來引用更新後的行中的列。
用OLD命名的列是僅僅讀的。你能夠引用它，但不能更改它。對於用NEW命名的列，假設具有
SELECT許可權，可引用它。在BEFORE觸發程式中，假設你具有UPDATE許可權，可使用“SET NEW.
col_name = value”更改它的值。這意味著，你能夠使用觸發程式來更改將要插入到新行中的值，
或用於更新行的值。
在BEFORE觸發程式中，AUTO_INCREMENT列的NEW值為0，不是實際插入新記錄時將自己主動生
成的序號。

通過使用BEGIN ... END結構，可以定義運行多條語句的觸發程式。在BEGIN塊中，還能使用儲存
子程式中同意的其它文法，如條件和迴圈等。可是，正如儲存子程式那樣，定義運行多條語句的
觸發程式時，假設使用mysql程式來輸入觸發程式，須要又一次定義語句分隔字元，以便可以在觸發
程式定義中使用字元“;”。在以下的示範範例中，示範了這些要點。在該示範範例中，定義了1個
UPDATE觸發程式，用於檢查更新每一行時將使用的新值，並更改值，使之位於0～100的範圍
內。它必須是BEFORE觸發程式，這是由於，須要在將值用於更新行之前對其進行檢查：
mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
    -> FOR EACH ROW
    -> BEGIN
    ->     IF NEW.amount < 0 THEN
    ->         SET NEW.amount = 0;
    ->     ELSEIF NEW.amount > 100 THEN
    ->         SET NEW.amount = 100;
    ->     END IF;
    -> END;//
mysql> delimiter ;
較為簡單的方法是，單獨定義儲存程式，然後使用簡單的CALL語句從觸發程式調用儲存程式。
假設你打算從數個觸發程式內部調用同樣的子程式，該方法也非常有協助。
在觸發程式的運行過程中，MySQL處理錯誤的方式例如以下：
·         假設BEFORE觸發程式失敗，不運行對應行上的操作。
·         僅當BEFORE觸發程式（假設有的話）和行操作均已成功運行，才運行AFTER觸發程式。
·         假設在BEFORE或AFTER觸發程式的運行過程中出現錯誤，將導致調用觸發程式的整個語
句的失敗。
·         對於事務性表，假設觸發程式失敗（以及由此導致的整個語句的失敗），該語句所啟動並執行
全部更改將復原。對於非事務性表，不能運行這類復原，因而，即使語句失敗，失敗之前所作的
不論什麼更改依舊有效。

例一：
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
    -> FOR EACH ROW SET @sum = @sum + NEW.amount;

mysql觸發器的作用及文法