前言
- 觸發(fā)器是與表有關(guān)的數(shù)據(jù)庫對象，在滿足定義的條件的時(shí)候(INSERT、UPDATE、DELETE事件動(dòng)作)觸發(fā)，并(After，Before)執(zhí)行觸發(fā)器中定義的語句集合。
- 這種特性可以協(xié)助應(yīng)用在數(shù)據(jù)庫端保證數(shù)據(jù)的完整性。
創(chuàng)建
1.語法
CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
tb_name ：需要建立觸發(fā)器的表名（只能是永久表，不能對臨時(shí)表創(chuàng)建觸發(fā)器）
trigger_name ：觸發(fā)器名稱，自行指定
trigger_time：觸發(fā)時(shí)機(jī)，取值BEFORE、AFTER
trigger_event ：觸發(fā)事件，INSERT、UPDATE、DELETE
trigger_stmt ： 觸發(fā)程序體，可以是一條SQL語句或是BEGIN和END包含的多條語句
2.類型
觸發(fā)類型受觸發(fā)時(shí)間和觸發(fā)動(dòng)作的影響，因此類型只有6種組合形式，并且同一個(gè)表的類型不可重復(fù)。
BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE
AFTER INSERT、AFTER UODATE、AFTER DELETE
3.BEGIN ... END
...
BEGIN
[statement_list]
END
statement_list代表一個(gè)或多個(gè)語句的列表，列表內(nèi)的每條SQL語句都必須用分號（;）來結(jié)尾。我們可以使用DELIMITERE指定自定義的定界符，用來區(qū)分觸發(fā)器和內(nèi)部SQL的結(jié)尾。
DELIMITER new_delemiter
new_delemiter 可以設(shè)為1個(gè)或多個(gè)長度的符號，我們可以把它修改為其他符號，如$：DELIMITER $，在結(jié)尾我們需要使用DELIMITER ;將分隔符再次修改成分號。
DELIMITER $
... --觸發(fā)器創(chuàng)建語句；
$ --提交創(chuàng)建語句；
DELIMITER ;
4.NEW和OLD
在trigger_body中，我們可以使用NEW表示將要插入的新行(INSERT的字段)，OLD表示將要?jiǎng)h除的舊行(DELETE的字段)。通過OLD，NEW中獲取其的內(nèi)容，方便在觸發(fā)操作中使用，下面是對應(yīng)事件是否支持OLD，NEW的對應(yīng)關(guān)系：
| DML | OLD | NEW |
|---|---|---|
| INSERT | × | √ |
| DELETE | √ | × |
| UPDATE | √ | √ |
UPDATE相當(dāng)于刪除舊行（OLD），然后插入新行（NEW），所以UPDATE同時(shí)支持OLD，NEW；
示例：
1.在插入數(shù)據(jù)之前計(jì)算出開始和結(jié)束時(shí)間差，并保存
DELIMITER$
drop trigger if exists tmp1_insert$
create trigger tmp1_insert
before insert on zcw
for each row
begin
set new.diff_day = DATEDIFF(new.end,new.start);
end$
DELIMITER ;
-- 插入語句
INSERT INTO zcw ( start, end) VALUES (NOW(), '2020-08-01');
start end diff_day
2020-07-31 2020-08-01 1
2.在更新A表的同時(shí)更新B表的字段
DELIMITER $
drop trigger if exists zcw_update$
create trigger zcw_update
after update on zcw
for each row
begin
update a set rules = new.rules where id=new.id;
end$
DELIMITER ;
-- 更新字段
UPDATE zcw set rules = 'boss' WHERE id = 1;
管理觸發(fā)器
- 查看觸發(fā)器
SHOW TRIGGERS [FROM schema_name]，schema_name表示的是表名稱。 - 刪除觸發(fā)器
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name
注意點(diǎn)
- 如果BEFORE觸發(fā)器執(zhí)行失敗，SQL無法正確執(zhí)行。
- SQL執(zhí)行失敗時(shí)，AFTER型觸發(fā)器不會觸發(fā)。
- AFTER類型的觸發(fā)器執(zhí)行失敗，SQL會回滾。
與事務(wù)的區(qū)別
事務(wù)是一種容器，是一種特殊的保護(hù)機(jī)制，是最小的單位，要么全部成功，要么全部失敗，也可以自由的控制提交和回滾。
觸發(fā)器的定位則是一種功能特性，類似于函數(shù)和存儲是一種功能，只有觸發(fā)了特定條件才會執(zhí)行，只有出錯(cuò)失敗才會回滾。
