教你如何使用MySQL觸發(fā)器

2022-09-08 17:22 作者:曉林電腦服務(wù) 0人讀過 | 我要投稿

MySQL 觸發(fā)器在添加、更新或刪除表行時(shí)對表應(yīng)用限制。

MySQL 中的列應(yīng)用了少量的值限制。例如，將列數(shù)據(jù)類型設(shè)置為tiny int而不是 null需要一個(gè)小的數(shù)值輸入。盡管如此，仍需要更多的限制來保持?jǐn)?shù)據(jù)的完整性。

本教程向您展示如何使用 MySQL 觸發(fā)器并為每種類型的觸發(fā)器提供示例。

IT外包|服務(wù)器虛擬化|數(shù)據(jù)存儲|數(shù)據(jù)備份|網(wǎng)絡(luò)故障維修|數(shù)據(jù)恢復(fù)|異地組網(wǎng) www.xiaolin.cc

先決條件

在數(shù)據(jù)庫服務(wù)器上運(yùn)行 MySQL 的系統(tǒng)
具有 root 權(quán)限的MySQL 用戶帳戶
基本 MySQL 命令的知識（請參閱我們可下載的MySQL 命令備忘單）

MySQL 中的觸發(fā)器是什么？

觸發(fā)器是一個(gè)命名的 MySQL 對象，它在表中發(fā)生事件時(shí)激活。觸發(fā)器是與特定表關(guān)聯(lián)的特定類型的存儲過程。

NEW觸發(fā)器允許使用和訪問表中的值以進(jìn)行比較OLD。修飾符的可用性取決于您使用的觸發(fā)事件：

在嘗試插入數(shù)據(jù)時(shí)檢查或修改值使NEW.<column name>修飾符可用。這是因?yàn)楸砀褚迅聻樾聝?nèi)容。相反，OLD.<column name>插入語句的值不存在，因?yàn)槭孪葲]有信息存在于其位置。

更新表格行時(shí)，兩個(gè)修飾符都可用。有OLD.<colum name>我們想更新為數(shù)據(jù)的NEW.<column name>數(shù)據(jù)。

最后，當(dāng)移除一行數(shù)據(jù)時(shí)，OLD.<column name>修飾符訪問移除的值。NEW.<column name>不存在，因?yàn)閯h除時(shí)沒有任何東西可以替換舊值。

注意：通過學(xué)習(xí)如何使用某些命令來重命名 MySQL 中的列，可以輕松管

理數(shù)據(jù)庫。

MySQL 觸發(fā)器示例

作為應(yīng)用觸發(fā)器的示例，將新值插入到person表中會產(chǎn)生與原始輸入不同的結(jié)果：

請注意，插入的名稱最初是小寫的。選擇表格時(shí)，第一個(gè)字母顯示為大寫。盡管沒有任何與常規(guī)插入語句不同的跡象，但觸發(fā)器在插入語句之前觸發(fā)以大寫名稱的第一個(gè)字母。

使用 MySQL 觸發(fā)器

與表關(guān)聯(lián)的每個(gè)觸發(fā)器都具有基于兩個(gè)因素的唯一名稱和功能：

1.時(shí)間。BEFORE或AFTER特定的行事件。

2.事件。INSERT,UPDATE或DELETE.

MySQL 觸發(fā)器根據(jù)激活時(shí)間和事件觸發(fā)，總共有六個(gè)獨(dú)特的觸發(fā)器組合。before 語句有助于在提交之前檢查數(shù)據(jù)并進(jìn)行更改，而 after 語句首先提交數(shù)據(jù)然后執(zhí)行語句。

一組動作的執(zhí)行自動發(fā)生，影響語句中所有插入、刪除或更新的行。

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

使用CREATE TRIGGER語句語法創(chuàng)建新觸發(fā)器：

CREATE TRIGGER <trigger name> <trigger time > <trigger event>ON <table name>FOR EACH ROW<trigger body>;

最佳做法是使用以下信息命名觸發(fā)器：

<trigger time>_<table name>_<trigger event>

例如，如果觸發(fā)器在名為employee的表上插入之前觸發(fā)，則最好的約定是調(diào)用觸發(fā)器：

before_employee_insert

或者，一種常見的做法是使用以下格式：

<table name>_<first letter of trigger time><first letter of trigger name>

表employee的前插入觸發(fā)器名稱如下所示：

employee_bi

對于受函數(shù)影響的每一行，觸發(fā)器在由<table name>定義的表上發(fā)生事件的特定時(shí)間執(zhí)行。

IT澶栧寘鏈嶅姟鍣ㄨ櫄鎷熷寲鏁版嵁瀛樺偍鏁版嵁澶囦喚鏁版嵁鎭㈠IT澶栧寘鏈嶅姟鍣ㄨ櫄鎷熷寲鏁版嵁瀛樺偍鏁版嵁澶囦喚鏁版嵁鎭㈠

刪除觸發(fā)器

要?jiǎng)h除觸發(fā)器，請使用以下DROP TRIGGER語句：

DROP TRIGGER <trigger name>;

或者，使用：

DROP TRIGGER IF EXISTS <trigger name>;

由于沒有觸發(fā)器，因此不會顯示錯(cuò)誤消息，因此不會打印警告。

創(chuàng)建示例數(shù)據(jù)庫

為具有以下結(jié)構(gòu)的觸發(fā)器示例代碼創(chuàng)建一個(gè)數(shù)據(jù)庫：

1.創(chuàng)建一個(gè)名為person的表，其中列有姓名和年齡。

CREATE TABLE person (name varchar(45), age int);

將樣本數(shù)據(jù)插入表中：

INSERT INTO person VALUES ('Matthew', 25), ('Mark', 20);

選擇表格以查看結(jié)果：

SELECT * FROM person;

2. 創(chuàng)建一個(gè)名為average_age的表，其中有一列名為average：

CREATE TABLE average_age (average double);

將平均年齡值插入表中：

INSERT INTO average_age SELECT AVG(age) FROM person;

選擇表格以查看結(jié)果：

SELECT * FROM average_age;

3. 創(chuàng)建一個(gè)名為person_archive的表，其中包含name、age和time列：

CREATE TABLE person_archive (name varchar(45),age int,time timestamp DEFAULT NOW());

注意：該函數(shù)NOW()記錄當(dāng)前時(shí)間。從我們的的MySQL日期函數(shù)指南和示例中了解有關(guān)日期和時(shí)間函數(shù)的更多信息。

IT外包服務(wù)器虛擬化數(shù)據(jù)存儲數(shù)據(jù)備份數(shù)據(jù)恢復(fù)IT外包服務(wù)器虛擬化數(shù)據(jù)存儲數(shù)據(jù)備份數(shù)據(jù)恢復(fù)

創(chuàng)建一個(gè) BEFORE INSERT 觸發(fā)器

要?jiǎng)?chuàng)建BEFORE INSERT觸發(fā)器，請使用：

CREATE TRIGGER <trigger name> BEFORE INSERTON <table name>FOR EACH ROW<trigger body>;

觸發(fā)器在BEFORE INSERT提交到數(shù)據(jù)庫表之前控制數(shù)據(jù)修改。BEFORE INSERT將名稱大寫以保持一致性、檢查輸入的長度或使用觸發(fā)器捕獲錯(cuò)誤輸入會在輸入新數(shù)據(jù)之前進(jìn)一步提供值限制。

BEFORE INSERT 觸發(fā)器示例

BEFORE INSERT在將數(shù)據(jù)插入人員表之前創(chuàng)建一個(gè)觸發(fā)器來檢查年齡值：

delimiter //CREATE TRIGGER person_bi BEFORE INSERTON personFOR EACH ROWIF NEW.age < 18 THENSIGNAL SQLSTATE '50001' SET MESSAGE_TEXT = 'Person must be older than 18.';END IF; //delimiter ;

插入數(shù)據(jù)會激活觸發(fā)器并在提交信息之前檢查age的值：

INSERT INTO person VALUES ('John', 14);

控制臺顯示描述性錯(cuò)誤消息。由于觸發(fā)器檢查失敗，數(shù)據(jù)未插入表中。

創(chuàng)建一個(gè) AFTER INSERT 觸發(fā)器

使用以下命令創(chuàng)建AFTER INSERT觸發(fā)器：

CREATE TRIGGER <trigger name> AFTER INSERTON <table name>FOR EACH ROW<trigger body>;

當(dāng)AFTER INSERT輸入的行生成更新另一個(gè)表所需的值時(shí)，觸發(fā)器很有用。

AFTER INSERT 觸發(fā)器示例

在person表中插入新行不會自動更新average_age表中的平均值。在person表上創(chuàng)建AFTER INSERT觸發(fā)器以在插入后更新average_age表：

delimiter //CREATE TRIGGER person_ai AFTER INSERTON personFOR EACH ROWUPDATE average_age SET average = (SELECT AVG(age) FROM person); //delimiter ;

在person表中插入一個(gè)新行會激活觸發(fā)器：

INSERT INTO person VALUES ('John', 19);

數(shù)據(jù)成功提交到person表并使用正確的平均值更新average_age表。

創(chuàng)建更新前觸發(fā)器

使用以下方法BEFORE UPDATE觸發(fā)：

CREATE TRIGGER <trigger name> BEFORE UPDATEON <table name>FOR EACH ROW<trigger body>;

觸發(fā)器與觸發(fā)器BEFORE UPDATE一起使用BEFORE INSERT。如果在插入數(shù)據(jù)之前存在任何限制，那么在更新之前也應(yīng)該存在限制。

BEFORE UPDATE 觸發(fā)器示例

如果在插入數(shù)據(jù)之前對person表有年齡限制，那么在更新信息之前也應(yīng)該有年齡限制。沒有BEFORE UPDATE觸發(fā)器，年齡檢查觸發(fā)器很容易避免。沒有什么可以將編輯限制為錯(cuò)誤的值。

IT澶栧寘鏈嶅姟鍣ㄨ櫄鎷熷寲鏁版嵁瀛樺偍鏁版嵁澶囦喚鏁版嵁鎭㈠IT澶栧寘鏈嶅姟鍣ㄨ櫄鎷熷寲鏁版嵁瀛樺偍鏁版嵁澶囦喚鏁版嵁鎭㈠

BEFORE UPDATE將觸發(fā)器添加到與觸發(fā)器具有相同主體的personBEFORE INSERT表中：

delimiter //CREATE TRIGGER person_bu BEFORE UPDATEON personFOR EACH ROWIF NEW.age < 18 THENSIGNAL SQLSTATE '50002' SET MESSAGE_TEXT = 'Person must be older than 18.';END IF; //delimiter ;

更新現(xiàn)有值會激活觸發(fā)器檢查：

UPDATE person SET age = 17 WHERE name = 'John';

將年齡更新為小于 18 的值會顯示錯(cuò)誤消息，并且信息不會更新。

創(chuàng)建 AFTER UPDATE 觸發(fā)器

使用以下代碼塊創(chuàng)建AFTER UPDATE觸發(fā)器：

CREATE TRIGGER <trigger name> AFTER UPDATEON <table name>FOR EACH ROW<trigger body>;

AFTER UPDATE觸發(fā)器有助于跟蹤已提交的數(shù)據(jù)更改。大多數(shù)情況下，插入信息后的任何更改也會在更新數(shù)據(jù)后發(fā)生。

AFTER UPDATE 觸發(fā)器示例

對person表中年齡數(shù)據(jù)的任何成功更新也應(yīng)該更新在average_age表中計(jì)算的中間平均值。

在更新person表中的一行后創(chuàng)建一個(gè)AFTER UPDATE觸發(fā)器來更新average_age表：

delimiter //CREATE TRIGGER person_au AFTER UPDATEON personFOR EACH ROWUPDATE average_age SET average = (SELECT AVG(age) FROM person); //delimiter ;

更新現(xiàn)有數(shù)據(jù)會更改person表中的值：

UPDATE person SET age = 21 WHERE name = 'John';

更新表person也會更新average_age表中的平均值。

創(chuàng)建一個(gè) BEFORE DELETE 觸發(fā)器

要?jiǎng)?chuàng)建BEFORE DELETE觸發(fā)器，請使用：

CREATE TRIGGER <trigger name> BEFORE DELETEON <table name>FOR EACH ROW<trigger body>;

出于安全原因，BEFORE DELETE觸發(fā)器是必不可少的。如果父表附加了任何子表，則觸發(fā)器有助于阻止刪除并防止孤立表。觸發(fā)器還允許在刪除之前歸檔數(shù)據(jù)。

BEFORE DELETE 觸發(fā)器示例

通過在表person上創(chuàng)建BEFORE DELETE觸發(fā)器來歸檔已刪除的數(shù)據(jù)并將值插入person_archive表：

delimiter //CREATE TRIGGER person_bd BEFORE DELETEON personFOR EACH ROWINSERT INTO person_archive (name, age)VALUES (OLD.name, OLD.age); //delimiter ;

從person表中刪除數(shù)據(jù)將數(shù)據(jù)歸檔到person_archive表中，然后再刪除：

DELETE FROM person WHERE name = 'John';

將值插入回person表中，將刪除數(shù)據(jù)的日志保留在person_archive表中：

INSERT INTO person VALUES ('John', 21);

觸發(fā)器對于BEFORE DELETE記錄任何表更改嘗試很有用。

創(chuàng)建一個(gè) AFTER DELETE 觸發(fā)器

使用以下方法AFTER DELETE觸發(fā)：

CREATE TRIGGER <trigger name> AFTER DELETEON <table name>FOR EACH ROW<trigger body>;

觸發(fā)器維護(hù)要求數(shù)據(jù)行在進(jìn)行更新之前消失的AFTER DELETE信息更新。

AFTER DELETE 觸發(fā)器示例

在person表上創(chuàng)建AFTER DELETE觸發(fā)器以使用新信息更新average_age表：

delimiter //CREATE TRIGGER person_ad AFTER DELETEON personFOR EACH ROWUPDATE average_age SET average = (SELECT AVG(person.age) FROM person); //delimiter ;

從person表中刪除一條記錄會使用新的平均值更新average_age表：

沒有AFTER DELETE觸發(fā)器，信息不會自動更新。

創(chuàng)建多個(gè)觸發(fā)器

MySQL 不支持同時(shí)觸發(fā)多個(gè)觸發(fā)器。但是，可以向同一個(gè)觸發(fā)器添加多個(gè)邏輯操作。使用BEGIN和END定界符來指示觸發(fā)器主體：

CREATE TRIGGER <trigger name> <trigger time > <trigger event>ON <table name>FOR EACH ROWBEGIN<trigger body>;END;

確保在創(chuàng)建具有多個(gè)操作的觸發(fā)器之前更改默認(rèn)分隔符。

顯示觸發(fā)器

列出數(shù)據(jù)庫中的所有觸發(fā)器：

SHOW triggers;

輸出顯示所有觸發(fā)器的列表，包括名稱和語句內(nèi)容：

還會顯示其他信息，例如創(chuàng)建時(shí)間和創(chuàng)建觸發(fā)器的用戶。

結(jié)論

MySQL 觸發(fā)器在特定事件發(fā)生之前或之后提供對數(shù)據(jù)的進(jìn)一步驗(yàn)證和控制。無論您是試圖防止錯(cuò)誤還是添加一致性限制，觸發(fā)器都有助于控制數(shù)據(jù)輸入、更新和刪除。

請記住，觸發(fā)器檢查是按行進(jìn)行的，這會導(dǎo)致性能因大量查詢而降低。

標(biāo)簽：

最美情侣中文字幕电影,在线麻豆精品传媒,在线网站高清黄,久久黄色视频