MySQL数据库：触发器Trigger
?
一、什么是觸發器：
觸發器是與表有關的數據庫對象，當觸發器所在表上出現指定事件并滿足定義條件的時候，將執行觸發器中定義的語句集合。觸發器的這種特性可以協助應用在數據庫端確保數據的完整性。觸發器是一個特殊的存儲過程，不同的是存儲過程要用call來調用，而觸發器不需要使用call，也不需要手工調用，它在插入，刪除或修改特定表中的數據時觸發執行，它比數據庫本身標準的功能有更精細和更復雜的數據控制能力。
?
二、創建觸發器：
1、語法：
CREATE TRIGGER trigger_name
trigger_time
trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
?
其中：
trigger_name：標識觸發器名稱，用戶自行指定；
trigger_time：標識觸發時機，取值為 BEFORE 或 AFTER；
trigger_event：標識觸發事件，取值為 INSERT、UPDATE 或 DELETE；
tbl_name：標識建立觸發器的表名，即在哪張表上建立觸發器；
trigger_stmt：觸發器程序體，可以是一句SQL語句，或者用 BEGIN 和 END 包含的多條語句。
由此可見，可以建立6種觸發器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE。另外有一個限制是不能同時在一個表上建立2個相同類型的觸發器，因此在一個表上最多建立6個觸發器。
2、Trigger_event?詳解：
MySQL 除了對 INSERT、UPDATE、DELETE 基本操作進行定義外，還定義了 LOAD DATA 和 REPLACE 語句，這兩種語句也能引起上述6中類型的觸發器的觸發。
（1）LOAD DATA 語句用于將一個文件裝入到一個數據表中，相當與一系列的 INSERT 操作。
（2）REPLACE 語句一般來說和 INSERT 語句很像，只是在表中有 primary key 或 unique 索引時，如果插入的數據和原來 primary key 或 unique 索引一致時，會先刪除原來的數據，然后增加一條新數據，也就是說，一條 REPLACE 語句有時候等價于一條。
（3）UPDATE?語句，有時候等價于一條 DELETE 語句加上一條 INSERT 語句。
INSERT 型觸發器：插入某一行時激活觸發器，可能通過 INSERT、LOAD DATA、REPLACE 語句觸發；
UPDATE 型觸發器：更改某一行時激活觸發器，可能通過 UPDATE 語句觸發；
DELETE 型觸發器：刪除某一行時激活觸發器，可能通過 DELETE、REPLACE 語句觸發。
3、BEGIN … END?詳解：
在MySQL中，BEGIN … END 語句的語法為：
BEGIN
[statement_list]
END
其中，statement_list 代表一個或多個語句的列表，列表內的每條語句都必須用分號（;）來結尾。
而在MySQL中，分號是語句結束的標識符，遇到分號表示該段語句已經結束，MySQL可以開始執行了。因此，解釋器遇到statement_list 中的分號后就開始執行，然后會報出錯誤，因為沒有找到和 BEGIN 匹配的 END。
這時就會用到 DELIMITER 命令，它是一條命令，不需要語句結束標識，語法為：
DELIMITER new_delemiter
new_delemiter 可以設為1個或多個長度的符號，默認的是分號（;），我們可以把它修改為其他符號，如$：
DELIMITER $
在這之后的語句，以分號結束，解釋器不會有什么反應，只有遇到了$，才認為是語句結束。注意，使用完之后，我們還應該記得把它給修改回來。
4、觸發器示例：
假設系統中有兩個表：
班級表 class(班級號 classID, 班內學生數 stuCount)
學生表 student(學號 stuID, 所屬班級號 classID)
要創建觸發器來使班級表中的班內學生數隨著學生的添加自動更新，代碼如下：
DELIMITER $
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID=new.classID);
update class set stuCount = c + 1 where classID = new.classID;
end$
DELIMITER ;
5、NEW?與 OLD 詳解：
（1）在 INSERT 型觸發器中，NEW 用來表示將要（BEFORE）或已經（AFTER）插入的新數據；
（2）在 UPDATE 型觸發器中，OLD 用來表示將要或已經被修改的原數據，NEW 用來表示將要或已經修改為的新數據；
（3）在 DELETE 型觸發器中，OLD 用來表示將要或已經被刪除的原數據；
使用方法： NEW.columnName （columnName 為相應數據表某一列名）
另外，OLD 是只讀的，而 NEW 則可以在觸發器中使用 SET 賦值，這樣不會再次觸發觸發器，造成循環調用（如每插入一個學生前，都在其學號前加“2013”）。
?
三、觸發器其他操作：
1、查看觸發器信息：
查看觸發器是指數據庫中已存在的觸發器的定義、狀態、語法信息等。
SHOW TRIGGERS [FROM schema_name];? ? --第一種
其中，schema_name 可以指定數據庫名。
在TRIGGERS 表中查看觸發器信息：
SELECT * FROM `information_schema`.`TRIGGERS` WHERE `TRIGGER_NAME`='xxxx';? ? ?--第二種
2、刪除觸發器
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;
?
四、觸發器相關：
1、觸發器的執行順序與異常機制：
MySQL的觸發器是按照BEFORE觸發器、行操作、AFTER觸發器的順序執行的，其中任何一步發生錯誤都不會繼續執行剩下的操作。如果是對事務表進行的操作，那么會整個作為一個事務被回滾，但是如果是對非事務表進行的操作，那么已經更新的記錄將無法回滾。
（1）如果 BEFORE 觸發器執行失敗，SQL 無法正確執行；
（2）SQL 執行失敗時，AFTER 型觸發器不會觸發；
（3）AFTER 類型的觸發器執行失敗，SQL 會回滾。
2、觸發器的使用限制：
（1）觸發器只能創建在永久表上，不能對臨時表創建觸發器；
（2）觸發程序不能調用將數據返回給客戶端的存儲程序，也不能使用CALL語句的動態SQL語句，但是允許存儲過程或者函數通過OUT或者INOUT類型的參數將數據返回觸發器是可以的，但是不能調用直接返回數據的過程。
（3）觸發器中不能使用開啟或結束事務的語句段，比如，開始事務（START TRANSACTION）、提交事務（COMMIT）或是回滾事務（ROLLBACK），但是回滾到一個保存點（SAVEPOINT是允許的，因為回滾到保存點不會結束事務）；
（4）外鍵不會激活觸發器；
（5）當使用基于行的復制時，從表上的觸發器不會因操作主表中的數據而激活。當使用基于語句的復制時，從表上的觸發器會被激活。
（6）觸發器中不允許返回值，因此觸發器中不能有返回語句，如果要立即停止一個觸發器，應該使用LEAVE語句；
3、觸發器的作用：
（1）安全性。可以基于數據庫的值使用戶具有操作數據庫的某種權利。
--可以基于時間限制用戶的操作，例如不允許下班后和節假日修改數據庫數據。
--可以基于數據庫中的數據限制用戶的操作，例如不允許股票的價格的升幅一次超過10%。
（2）審計。可以跟蹤用戶對數據庫的操作。???
--審計用戶操作數據庫的語句。
--把用戶對數據庫的更新寫入審計表。
（3）實現復雜的數據完整性規則：
--實現非標準的數據完整性檢查和約束。觸發器可產生比規則更為復雜的限制。與規則不同，觸發器可以引用列或數據庫對象。例如，觸發器可回退任何企圖吃進超過自己保證金的期貨。
--提供可變的缺省值。
（4）實現復雜的非標準的數據庫相關完整性規則。觸發器可以對數據庫中相關的表進行連環更新。例如，在auths表author_code列上的刪除觸發器可導致相應刪除在其它表中的與之匹配的行。
--修改或刪除時級聯修改或刪除其它表中的與之匹配的行。
--在修改或刪除時把其它表中的與之匹配的行設成NULL值。
--在修改或刪除時把其它表中的與之匹配的行級聯設成缺省值。
--觸發器能夠拒絕或回退那些破壞相關完整性的變化，取消試圖進行數據更新的事務。當插入一個與其主健不匹配的外部鍵時，這種觸發器會起作用。例如，可以在books.author_code?列上生成一個插入觸發器，如果新值與auths.author_code列中的某值不匹配時，插入被回退。
（5）同步實時地復制表中的數據。
（6）自動計算數據值，如果數據的值達到了一定的要求，則進行特定的處理。例如，如果公司的帳號上的資金低于5萬元則立即給財務人員發送警告數據。
?
?
相關博客：https://www.cnblogs.com/duodushu/p/5446384.html
與50位技術專家面對面20年技術見證，附贈技術全景圖總結
以上是生活随笔為你收集整理的MySQL数据库：触发器Trigger的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： MySQL数据库：视图View
- 下一篇： MySQL数据库：游标Cursor