怎么創(chuàng)建MySQL的TRIGGER觸發(fā)器

這篇文章主要講解了“怎么創(chuàng)建MySQL的TRIGGER觸發(fā)器”，文中的講解內(nèi)容簡(jiǎn)單清晰，易于學(xué)習(xí)與理解，下面請(qǐng)大家跟著小編的思路慢慢深入，一起來(lái)研究和學(xué)習(xí)“怎么創(chuàng)建MySQL的TRIGGER觸發(fā)器”吧！

MySQL TRIGGER（觸發(fā)器）

數(shù)據(jù)庫(kù)觸發(fā)器是數(shù)據(jù)庫(kù)中所維護(hù)的命名數(shù)據(jù)庫(kù)對(duì)象，將在修改表中數(shù)據(jù)時(shí)被激活。使用觸發(fā)器可以提高表中數(shù)據(jù)的功能和安全性級(jí)別。可以使用觸發(fā)器控制對(duì)特定數(shù)據(jù)的訪問(wèn)權(quán)限、執(zhí)行特定日志記錄或?qū)?shù)據(jù)本身進(jìn)行審計(jì)。

觸發(fā)器是在修改表數(shù)據(jù)時(shí)激活的命名數(shù)據(jù)庫(kù)對(duì)象。它們可以：

?  在插入或更新數(shù)據(jù)之前對(duì)數(shù)據(jù)進(jìn)行檢查并驗(yàn)證刪除和更新

?  充當(dāng)數(shù)據(jù)過(guò)濾器，在插入或更新之前修改超出范圍的數(shù)據(jù)

?  修改INSERT、UPDATE 和DELETE 的行為方式

?  對(duì)于不支持外鍵的存儲(chǔ)引擎，模仿外鍵的行為

?  提供日志記錄功能

?  自動(dòng)創(chuàng)建匯總表

1.1.        創(chuàng)建觸發(fā)器

CREATE TRIGGER 語(yǔ)句：

CREATE TRIGGER trigger_name

{ BEFORE | AFTER }

{ INSERT | UPDATE | DELETE }

ON table_name FOR EACH ROW

triggered_statement

示例：

CREATE TRIGGER City_AD AFTER DELETE

ON City FOR EACH ROW

INSERT INTO DeletedCity (ID, Name) VALUES (OLD.ID, OLD.Name);

trigger_name 是為觸發(fā)器指定的名稱，而table_name 是要與觸發(fā)器關(guān)聯(lián)的表的名稱。BEFORE 和AFTER 指示激活觸發(fā)器的時(shí)間（是在觸發(fā)事件之前或是之后），而INSERT、UPDATE 或DELETE 指示具體的事件。

注：表名OLD 和NEW 是指觸發(fā)器可查看的虛擬表。這些表分別包含UPDATE 或DELETE語(yǔ)句所修改的數(shù)據(jù)的舊版本，或INSERT 或UPDATE 語(yǔ)句所添加的數(shù)據(jù)的新版本。

1.2.        觸發(fā)器事件

BEFORE 和AFTER

BEFORE 和AFTER 關(guān)鍵字是指觸發(fā)器的激活時(shí)間，相對(duì)于數(shù)據(jù)修改語(yǔ)句（INSERT、UPDATE 或DELETE）將更改寫入底層數(shù)據(jù)庫(kù)的時(shí)間。

BEFORE 關(guān)鍵字可使觸發(fā)器在涉及的數(shù)據(jù)修改之前執(zhí)行?？墒褂肂EFORE 觸發(fā)器捕獲無(wú)效數(shù)據(jù)條目并在寫入表之前對(duì)其進(jìn)行更正或拒絕。

– BEFORE INSERT：在添加新數(shù)據(jù)之前觸發(fā)

– BEFORE UPDATE：在使用新數(shù)據(jù)更新（或覆蓋）現(xiàn)有數(shù)據(jù)之前觸發(fā)

– BEFORE DELETE：在刪除數(shù)據(jù)之前觸發(fā)

AFTER 關(guān)鍵字定義在數(shù)據(jù)修改成功后執(zhí)行的觸發(fā)器。可使用AFTER 觸發(fā)器記錄或?qū)徲?jì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)修改。

– AFTER INSERT：在添加新數(shù)據(jù)之后觸發(fā)

– AFTER UPDATE：在使用新數(shù)據(jù)更新（或覆蓋）現(xiàn)有數(shù)據(jù)之后觸發(fā)

– AFTER DELETE：在刪除數(shù)據(jù)之后觸發(fā)

1.3.        觸發(fā)器錯(cuò)誤處理

MySQL 按如下方式處理觸發(fā)器執(zhí)行期間發(fā)生的錯(cuò)誤：

?  BEFORE 觸發(fā)器失敗，則包含相應(yīng)行操作的事務(wù)將回滾。

?  AFTER 觸發(fā)器執(zhí)行，AFTER觸發(fā)器事件和行操作必須成功執(zhí)行。

?  對(duì)于非事務(wù)表，事務(wù)不可用。只有觸發(fā)了觸發(fā)器的語(yǔ)句會(huì)回滾。

觸發(fā)器失敗后，MySQL 會(huì)回滾包含導(dǎo)致觸發(fā)器觸發(fā)的語(yǔ)句的事務(wù)。

對(duì)于非事務(wù)表，無(wú)法完成此類回滾。因此，雖然語(yǔ)句失敗，但在發(fā)生錯(cuò)誤之前執(zhí)行的所有更改仍然會(huì)生效。

1.4.        檢查觸發(fā)器

SHOW CREATE TRIGGER trigger_name

此語(yǔ)句返回可用于重新創(chuàng)建指定觸發(fā)器的具體字符串。您必須知道觸發(fā)器的名稱才能運(yùn)行此語(yǔ)句；對(duì)于SHOW CREATE TRIGGER 語(yǔ)句，不存在LIKE 或WHERE 語(yǔ)法。

SHOW TRIGGERS

此語(yǔ)句為MySQL 擴(kuò)展。它可返回觸發(fā)器的特征，如數(shù)據(jù)庫(kù)、名稱、類型、創(chuàng)建者以及創(chuàng)建和修改日期。此語(yǔ)句有一個(gè)優(yōu)點(diǎn)：可基于LIKE 模式或WHERE 子句中提供的條件來(lái)顯示特定觸發(fā)器。如果未指定條件，則此語(yǔ)句會(huì)顯示所有觸發(fā)器的信息。

INFORMATION_SCHEMA.TRIGGERS：

此語(yǔ)句包含SHOW 命令所顯示的所有數(shù)據(jù)，能夠完整地呈現(xiàn)在所有數(shù)據(jù)庫(kù)中可用的觸發(fā)器。

1.5.        刪除觸發(fā)器

使用如下語(yǔ)法可顯式刪除觸發(fā)器：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

使用DROP TRIGGER trigger_name 時(shí)，服務(wù)器會(huì)在當(dāng)前模式中查找該觸發(fā)器的名稱。如果要?jiǎng)h除其他模式中的觸發(fā)器，請(qǐng)包含模式名稱。使用IF EXISTS 可防止因嘗試刪除不存在的觸發(fā)器而出現(xiàn)的錯(cuò)誤。通過(guò)刪除針對(duì)其定義觸發(fā)器的表或者包含觸發(fā)器的數(shù)據(jù)庫(kù)，可隱式刪除觸發(fā)器；

1.6.        對(duì)觸發(fā)器的限制

?  不允許使用的語(yǔ)句包括：

l  SQL 預(yù)處理語(yǔ)句

l  顯示或隱式COMMIT 和ROLLBACK

l  返回結(jié)果集的語(yǔ)句，例如，不包含INTO var_list 子句的SELECT 語(yǔ)句

l  FLUSH 語(yǔ)句

l  用于修改要應(yīng)用觸發(fā)器的表的語(yǔ)句

l  SHOW 語(yǔ)句

?  以下更改不會(huì)觸發(fā)觸發(fā)器：

l  級(jí)聯(lián)外鍵所導(dǎo)致的更改

l  在基于行的復(fù)制過(guò)程中導(dǎo)致的更改

請(qǐng)通過(guò)使用SELECT ...INTO var_list，或者通過(guò)使用游標(biāo)和FETCH 語(yǔ)句的方法在觸發(fā)器中處理結(jié)果集。

1.7.        觸發(fā)器權(quán)限

?  要執(zhí)行CREATE TRIGGER 和DROP TRIGGER 命令，需要有TRIGGER 權(quán)限。

?  在觸發(fā)器中使用OLD 和NEW需要的權(quán)限：要使用SET NEW.col_name = value 為列賦值，需要對(duì)該列擁有UPDATE 權(quán)限；要在表達(dá)式中使用NEW.col_name 以引用新的列值，需要對(duì)該列擁有SELECT 權(quán)限。

感謝各位的閱讀，以上就是“怎么創(chuàng)建MySQL的TRIGGER觸發(fā)器”的內(nèi)容了，經(jīng)過(guò)本文的學(xué)習(xí)后，相信大家對(duì)怎么創(chuàng)建MySQL的TRIGGER觸發(fā)器這一問(wèn)題有了更深刻的體會(huì)，具體使用情況還需要大家實(shí)踐驗(yàn)證。這里是億速云，小編將為大家推送更多相關(guān)知識(shí)點(diǎn)的文章，歡迎關(guān)注！