快捷導(dǎo)航

Mysql觸發(fā)器語法解讀(附帶簡單實用例子)

更新時間：2023年08月30日 11:01:17 作者：good_good_xiu

這篇文章主要介紹了Mysql觸發(fā)器語法解讀(附帶簡單實用例子),具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教

觸發(fā)器語法

DEFINER：定義該觸發(fā)器的用戶和主機地址，一般默認為當(dāng)前用戶和主機地址。
trigger_name：觸發(fā)器名稱 AFTER|BEFORE：觸發(fā)器觸發(fā)狀態(tài)，二選一。
INSERT|UPDATE|DELETE：觸發(fā)器觸發(fā)狀態(tài)，三選一。
table_name：監(jiān)控的數(shù)據(jù)表名稱。
FOR EACH ROW：行級觸發(fā)器，修改一行數(shù)據(jù)觸發(fā)一次。不寫就默認語句級觸發(fā)器，不管修改多少行數(shù)據(jù)，只執(zhí)行一次。

-- 數(shù)據(jù)庫操作工具方法（navicat、sqlyog）
CREATE DEFINER=`root`@`localhost` TRIGGER trigger_name AFTER|BEFORE INSERT|UPDATE|DELETE ON table_name
FOR EACH ROW -- 行級觸發(fā)器，修改一行數(shù)據(jù)觸發(fā)一次。不寫就默認語句級觸發(fā)器，不管修改多少行數(shù)據(jù)，只執(zhí)行一次。
BEGIN
	... -- 具體執(zhí)行語句
END

命令行方式

-- 命令行方式
--先更改語句結(jié)束符號
delimiter ##  -- 切換自定義結(jié)束符號，在可視化操作頁面不需要，在命令行中創(chuàng)建觸發(fā)器則需要。
-- 再創(chuàng)建觸發(fā)器
CREATE TRIGGER trigger_name AFTER|BEFORE INSERT|UPDATE|DELETE ON table_name
FOR EACH ROW -- 行級觸發(fā)器
BEGIN
	... -- 具體執(zhí)行語句
END
## -- 代表創(chuàng)建觸發(fā)器語句結(jié)束，這樣就不會執(zhí)行到分號;的時候暫停執(zhí)行了。
delimiter ; --恢復(fù)mysql默認語句結(jié)束符號

實用案例（常用after類型觸發(fā)器）

before觸發(fā)器一般用于在操作數(shù)據(jù)前，對新數(shù)據(jù)的值進行修改。

例如：

考試時長計算，set new.考試時長 = new endtime - new starttime（很少使用）

-- 在插入bysj_et表后觸發(fā)
DROP TRIGGER if EXISTS testi; -- 如果存在testi觸發(fā)器則刪除
CREATE TRIGGER testi AFTER INSERT ON bysj_et
FOR EACH ROW
BEGIN
	INSERT INTO bysj_dt VALUES (new.id,new.et_name);
END
-- 在刪除bysj_et表后觸發(fā)
DROP TRIGGER if EXISTS testd; -- 如果存在testd觸發(fā)器則刪除
CREATE TRIGGER testd AFTER DELETE ON bysj_et
FOR EACH ROW
BEGIN
	DELETE FROM bysj_dt WHERE id = old.id AND dt_name = old.et_name;
END
-- 在更新bysj_et表后觸發(fā)
DROP TRIGGER if EXISTS testu; -- 如果存在testu觸發(fā)器則刪除
CREATE TRIGGER testu AFTER UPDATE ON bysj_et
FOR EACH ROW
BEGIN
	-- SET @oid = old.id; -- 獲取更新前舊數(shù)據(jù)行id
	-- SET @odt_name = old.et_name; -- 獲取更新前舊數(shù)據(jù)行et_name
	-- SET @nid = new.id; -- 獲取新數(shù)據(jù)行id
	-- SET @ndt_name = new.et_name; -- 獲取新數(shù)據(jù)行et_name
	--UPDATE bysj_dt SET id = @nid,dt_name = @ndt_name WHERE id = @oid AND dt_name = @odt_name; --具體執(zhí)行語句
	-- 上面的語句熟悉了之后可以優(yōu)化成下面這樣
	UPDATE bysj_dt SET id = new.id,dt_name = new.et_name WHERE id = old.id AND dt_name = old.et_name;
END

總結(jié)（踩坑記錄）

1.new.字段的值可以在before類型的觸發(fā)器中進行賦值和取值，在after類型觸發(fā)器中只能取值。(在after類型觸發(fā)器中進行對new數(shù)據(jù)行賦值操作會報錯。因為after是在操作之后，已經(jīng)產(chǎn)生了新數(shù)據(jù)行，不可修改。)

2.在insert操作中，只有new數(shù)據(jù)行，沒有old數(shù)據(jù)行。(使用old關(guān)鍵字會報錯)

3.在update操作中，new數(shù)據(jù)行和old數(shù)據(jù)行存在。

4.在delete操作中，只有old數(shù)據(jù)行。(使用new關(guān)鍵字會報錯)

5.在mysql5.7之前的版本，同一張表中，不能存在兩個類型一樣的觸發(fā)器。如果想在一個觸發(fā)器種實現(xiàn)兩種不同的處理語句執(zhí)行，可以增加條件判斷。

例如：使用if語句

CREATE DEFINER=`root`@`localhost` TRIGGER testi AFTER INSERT ON bysj_et
FOR EACH ROW
BEGIN
	IF(new.id = 6) -- 當(dāng)新id為6時
	THEN IF(new.et_name = '6') -- 當(dāng)新id為6，并且name也為6才執(zhí)行插入語句
		THEN INSERT INTO bysj_dt VALUES (new.id,new.et_name);
		END IF;
	END IF;
END;

或使用case when語句

CREATE DEFINER=`root`@`localhost` TRIGGER testi AFTER INSERT ON bysj_et
FOR EACH ROW
BEGIN
	CASE 
	WHEN new.id = 6 AND new.et_name = '6' THEN
		INSERT INTO bysj_dt VALUES (new.id,'等于6');
	WHEN new.id < 6 THEN
		INSERT INTO bysj_dt VALUES (new.id,'小于6');
	ELSE
		INSERT INTO bysj_dt VALUES (100,'100');
END CASE;
END

6.在mysql5.7之后的版本可以存在兩個類型的觸發(fā)器。