(2021.12.23 Thur)
觸發(fā)器有時(shí)也稱作事件-條件-動(dòng)作規(guī)則(event-condition-action rule)，或ECA。觸發(fā)器有如下特點(diǎn)
- 僅當(dāng)數(shù)據(jù)庫程序員生命的事件發(fā)生時(shí)，觸發(fā)器被激活。允許的事件種類是對某個(gè)特定關(guān)系的
INSERT,UPDATE和DELETE。很多SQL系統(tǒng)中允許的另一種事件是事務(wù)的結(jié)束。 - 當(dāng)觸發(fā)器被事件激活時(shí)，trigger測試觸發(fā)的條件(condition)。如果條件不成立，則響應(yīng)該事件的觸發(fā)器不做任何事。
- 如果trigger聲明的條件滿足，則與該觸發(fā)器相連的動(dòng)作(action)有DBMS執(zhí)行。動(dòng)作可以是以某種方式修改事件的結(jié)果，甚至可以是撤銷事件所在的事務(wù)。事實(shí)上，動(dòng)作可以使任何數(shù)據(jù)庫操作的序列，包括與觸發(fā)事件毫無關(guān)聯(lián)的操作。
觸發(fā)器的特征
- 條件檢察和動(dòng)作可以在觸發(fā)事件執(zhí)行之前的
數(shù)據(jù)庫的狀態(tài)(state of the database)(即當(dāng)前所有關(guān)系的實(shí)例)上或在出發(fā)動(dòng)作被執(zhí)行后的狀態(tài)上執(zhí)行。BEFORE/AFTER - 條件和動(dòng)作可以引用元組的舊值和/或觸發(fā)動(dòng)作被執(zhí)行后的狀態(tài)上執(zhí)行。
NEW/OLD - 更新事件可以被局限到某個(gè)特定的屬性或某一些屬性
- 可選擇動(dòng)作執(zhí)行的方式：
a) 一次只對一個(gè)更新元組(row-level trigger，行級觸發(fā)器) 或
b) 一次針對在數(shù)據(jù)庫操作中被改變的所有元組(statement-level trigger，語句級觸發(fā)器，記住一個(gè)SQL更新語句可以影響許多元組)。
觸發(fā)器的格式
CREATE TRIGGER <trigger_name>
<timing> <action> [OF <field1>] ON <table1>
[REFERENCING
OLD ROW AS <name_old>
NEW ROW AS <name_new>
]
FOR EACH <method>
BEGIN
<sql_scripts>
END
記得在MySQL中還要在該格式的前后加上DELIMITER\\和DELIMITER;。
- <trigger_name>: 觸發(fā)器的命名
- <timing>: 時(shí)機(jī)，只有兩個(gè)選項(xiàng)
BEFORE和AFTER，代表著觸發(fā)動(dòng)作之前的前/后對特定表格做修改 - <action>: 觸發(fā)的條件動(dòng)作，即當(dāng)某個(gè)動(dòng)作發(fā)生時(shí)，觸發(fā)后面的操作。有三個(gè)動(dòng)作，
INSERT，UPDATE和DELETE - <field1>: 可選。代表著條件動(dòng)作中需要修改的列
- <table1>: 觸發(fā)條件動(dòng)作的表格
- <name_old/name_new>: 可選。該
REFERENCING子句允許觸發(fā)器的動(dòng)作和動(dòng)作引用正被修改的元組，在更新的情況下，蓋子局允許給改變之前和之后的元組命名。如果不使用該子句，則只需要用NEW和OLD代表修改前后的元組即可。 - <method>: 有
ROW和STATEMENT兩種可選，告訴觸發(fā)器支隊(duì)每個(gè)修改的行執(zhí)行一次，還是對由SQL語句做的所有修改執(zhí)行一次的子句。默認(rèn)是FOR EACH STATEMENT - <sql_scripts>: 被觸發(fā)器觸發(fā)的動(dòng)作
例1，該案例中每次對MovieExec表中的netWorth字段做按行的UPDATE之后，并且修改前的數(shù)據(jù)大于修改后的數(shù)據(jù)的情況下，設(shè)定MoveExec中的netWorth保留為原數(shù)據(jù)。
DELIMITER &&
CREATE TRIGGER NetWorthTrigger
AFTER UPDATE OF netWorth ON MovieExec
REFERENCING
OLD ROW AS OldTuple
NEW ROW AS NewTuple
FOR EACH ROW
WHEN (OldTuple.netWorth > NewTuple.netWorth)
UPDATE MovieExec
SET netWorth = OldTuple.netWorth
WHERE cert# = NewTuple.cert#;
DELIMITER;
new/old
(2021.12.24 Fri)
每行數(shù)據(jù)在操作前后都有一個(gè)狀態(tài)，trigger將沒有操作之前的狀態(tài)保存到old中，將操作之后的保存到new關(guān)鍵字中。調(diào)用格式是
new/old.<field_name>
-
INSERT關(guān)鍵字：只有new沒有old， -
UPDATE關(guān)鍵字：有new也有old，old表示更新前的數(shù)據(jù)，new表示更新后的數(shù)據(jù) -
DELETE關(guān)鍵字：只有old沒有new，old表示被刪除前的數(shù)據(jù)
這里的new/old代表的是觸發(fā)條件的數(shù)據(jù)。比如，對關(guān)系A的修改加入或刪除會觸發(fā)關(guān)系B變化，這里的A可以被new/old指代。在觸發(fā)的動(dòng)作里，可通過new/old.<field_name>引用觸發(fā)條件關(guān)系的數(shù)據(jù)。
行級、語句級觸發(fā)器
一旦有合適類型的語句被執(zhí)行，語句級觸發(fā)器就被執(zhí)行，而不問它實(shí)際上會影響多少個(gè)元組。例如，如果用SQL更新語句更新整個(gè)表，語句級的修改觸發(fā)器將只被執(zhí)行一次，而元組級觸發(fā)器將對要修改的元組一次一個(gè)的執(zhí)行。
下面這個(gè)例子，在更新MovieExec的元組過程中，平均凈資產(chǎn)可以暫時(shí)的低于500000，當(dāng)所有變更結(jié)束時(shí)，其凈資產(chǎn)值將超過500000。約束要做的是，若語句執(zhí)行結(jié)束后，凈資產(chǎn)值仍然低于500000，則整個(gè)一組更新操作被拒絕。
CREATE TRIGGER AvgNetWorthTrigger
AFTER UPDATE OF netWorth ON MovieExec
REFERENCING
OLD TABLE AS OldStuff
NEW TABLE AS NewStuff
FOR EACH STATEMENT
WHEN (500000 > (SELECT AVG(netWorth) FROM MovieExec))
BEGIN
DELETE FROM MovieExec
WHERE (name, address, cert#, netWorth) IN NewStuff;
INSERT INTO MovieExec
(SELECT * FROM OldStuff);
END;
命令
查看觸發(fā)器
SHOW TRIGGERS;
SHOW TRIGGERS/G;
查看觸發(fā)器的創(chuàng)建語句
SHOW CREATE TRIGGER <trigger_name>;
SHOW CREATE TRIGGER <trigger_name>/G;
刪除觸發(fā)器
DROP TRIGGER <trigger_name>;
限制
- 觸發(fā)器不能使用CALL 語句來將數(shù)據(jù)返回給客戶端或使用動(dòng)態(tài)SQL的存儲過程。但允許存儲過程通過OUT或INOUT 參數(shù)將數(shù)據(jù)返回到觸發(fā)器
- 觸發(fā)不能使用事務(wù)相關(guān)的語句，如 START TRANSACTION，COMMIT或ROLLBACK。因?yàn)橛|發(fā)器對update,delete,insert等事件做了處理，并且是按照before，SQL語句，after的順序來執(zhí)行的，一旦某一步出錯(cuò)，就會回滾數(shù)據(jù)。如果在觸發(fā)器中使用事務(wù)，就會產(chǎn)生矛盾。
Reference
1 Jeffery U.等著，岳麗華等譯，數(shù)據(jù)庫系統(tǒng)基礎(chǔ)教程，機(jī)械工業(yè)出版社
