MySQL觸發器使用
觸發器：
觸發器的使用場景以及相應版本：
觸發器可以使用的MySQL版本：
- 版本：MySQL5以上
使用場景例子：
- 每當增加一個顧客到某個資料庫表時，都檢查其電話號碼格式是否正確，州的縮寫是否為大寫
- 每當訂購一個產品時，都從庫存數量中減去訂購的數量
- 無論何時刪除一行，都在某個存檔表中保留一個副本
即：在某個表發生更改時自動處理。
如遇到觸發器報錯「Not allowed to return a result set from a trigger」；請划到最後看詳解；
觸發器的使用：
創建基本的觸發器：
CREATE TRIGGER newproduct AFTER INSERT on products FOR EACH ROW
BEGIN
DECLARE msg VARCHAR(100);
SET msg = "products added";
SIGNAL SQLSTATE 'HY000' SET message_text = msg;
END
結果：
INSERT INTO products VALUES('demo2','1003','xiaoguo','66.6','hello world')
> 1644 - products added
> 時間: 0.035s
解釋：
首先創建一個觸發器：
#newproduct 觸發器的名字
CREATE TRIGGER newproduct
觸發的時機：
BEFORE：觸發器在觸發他們的語句之前觸發
AFTER：觸發器在觸發他們的語句完成後觸發
在這裡我們使用的after;也就是在插入結束後觸發條件；
DECLARE msg VARCHAR(100);
注意：declare語句是在複合語句中聲明變數的指令；如果不聲明msg,執行語句時，MySQL報錯；
SIGNAL SQLSTATE 'HY000' SET message_text = msg;
如果該SIGNAL語句指示特定SQLSTATE值，則該值用於表示指定的條件
“HY000」被稱為「一般錯誤”：
如果命令出現一般錯誤，則會觸發後面的message中的消息;
註：該語句只是個人理解，也是一知半解，如果有更好的解釋，歡迎留言。
觸發的條件以BEGIN開始，END結束。
觸發事件：
- insert
- update
- delete
刪除觸發器：
-- 刪除觸發器
DROP TRIGGER newproduct;
INSERT觸發器：
insert觸發器在insert語句執行之前或者之後執行，需要注意以下幾點：
- 在insert觸發器程式碼內。可以引用一個名為NEW的虛擬表，訪問被插入的行；
- 在before insert觸發器中，NEW中的值也可以被更新（允許更改被插入的值）
- 對於AUTO_INCREMENT列，NEW在insert執行之前包含0，在insert執行之後包含新的自動生成值
例子：插入一個新的訂單時，生成一個新的訂單號保存到order_num
CREATE TRIGGER neworder AFTER INSERT ON orders for EACH ROW
SELECT NEW.order_num into @ee;
insert INTO orders(order_date,cust_id) VALUES(NOW(),10001);
SELECT @ee as num;
drop TRIGGER neworder;
解釋：
創建一個neworder的觸發器，在插入之後執行，且對每個插入行執行，在insert中有一個與orders表一摸一樣的虛表，用NEW 表示；
SELECT NEW.order_num into @a;
在虛表中找到我們插入的數據的編號，將標號保存在a變數中；
檢測：
insert INTO orders(order_date,cust_id) VALUES(NOW(),10001);
SELECT @ee as num;
插入數據，輸出插入數據的編號
刪除：
drop TRIGGER neworder;
刪除觸發器。
例二：
在COURSE表上創建觸發器，檢查插入時是否出現課程名相同的記錄，若有則不操作。
CREATE TRIGGER trg_course_in
BEFORE INSERT ON course
FOR EACH ROW
BEGIN
DECLARE msg VARCHAR(100);
IF EXISTS (SELECT * FROM course where cname=NEW.cname) THEN
SET msg='不能輸入相同名稱的課程';
SIGNAL SQLSTATE 'HY000' SET message_text = msg;
END IF;
END
例三：向student表中插入資訊時，檢查ssex的值必須為男或女。
CREATE TRIGGER trg_ssex AFTER INSERT on student FOR EACH ROW
BEGIN
DECLARE msg VARCHAR(100);
IF(NEW.ssex not in('男','女')) THEN
SET msg ='性別必須為男或女';
SIGNAL SQLSTATE 'HY000' SET message_text = msg;
END IF
END
UPDATE觸發器：
- 在update觸發器的程式碼中，可以引用一個名為OLD的虛擬表訪問以前的值，即：update未執行前的值，還可以引用一個名為NEW的虛擬表訪問新更新的值；
- 在before update觸發器中，NEW中的值可能也被更新（允許修改將要用於update語句中的值）；
- OLD中的值全部只讀，不能更新。
例一：保證州名縮寫為大寫
CREATE TRIGGER UPDATEevendor BEFORE UPDATE on vendors
FOR EACH ROW SET new.vend_state =UPPER(new.vend_state);
UPDATE vendors SET vend_state='hw' where vend_id='1001';
DROP TRIGGER UPDATEevendor;
註：upper:將文本轉換為大寫：
例二：不允許修改student表中的學號sno，如果修改該列則顯示錯誤資訊並取消操作。
CREATE TRIGGER trg_student_updateSno BEFORE UPDATE
FOR EACH ROW
BEGIN
DECLARE msg VARCHAR(100);
IF NEW.sno <> OLD.sno THEN
SET msg='不允許修改sno';
SIGNAL SQLSTATE 'HY000' SET message_text = msg;
END IF;
END
DELETE觸發器：
在DELETE觸發器在delete語句執行之前或之後執行：
- 在delete觸發器程式碼內，可以引用OLD的虛擬表，訪問被刪除的行；
- OLD中的值全部都是只讀，不能更新
例子：
使用old保存將要被刪除的行到一個存檔表中
首先先創建一個與orders相似的表：
CREATE TABLE archive_orders LIKE orders;
-- 創建一個刪除的觸發器
CREATE TRIGGER deleteorder BEFORE DELETE on orders
for EACH ROW BEGIN
INSERT INTO archive_orders(order_num,order_date,cust_id) VALUES(old.order_num,old.order_date,old.cust_id);
END
解釋:
在刪除order表中行中資訊時，將刪除的資訊保存到archive_orders中；
刪除原表中一行：
DELETE FROM orders WHERE order_num='20014';
查看效果：
SELECT * FROM archive_orders;
結束：
註：如果遇到觸發器報錯「Not allowed to return a result set from a trigger」
- 原因：因為從MySQL5以後不支援觸發器返回結果集
- 解決方法：在後面語句後面添加 into @變數名
- 取數據：select @變數名
詳細解釋：//www.programmersought.com/article/3237975256/
創建用戶變數：//blog.csdn.net/JesseYoung/article/details/40779631
感謝各位看到最後!
