MySQL觸發(fā)器的使用場景及方法實(shí)例

觸發(fā)器：

觸發(fā)器的使用場景以及相應(yīng)版本：

觸發(fā)器可以使用的MySQL版本：

• 版本：MySQL5以上
  

使用場景例子：

1. 每當(dāng)增加一個(gè)顧客到某個(gè)數(shù)據(jù)庫表時(shí)，都檢查其電話號(hào)碼格式是否正確，州的縮寫是否為大寫
2. 每當(dāng)訂購一個(gè)產(chǎn)品時(shí)，都從庫存數(shù)量中減去訂購的數(shù)量
3. 無論何時(shí)刪除一行，都在某個(gè)存檔表中保留一個(gè)副本
   

即：在某個(gè)表發(fā)生更改時(shí)自動(dòng)處理。

如遇到觸發(fā)器報(bào)錯(cuò)“Not allowed to return a result set from a trigger”；請(qǐng)劃到最后看詳解；

觸發(fā)器的使用：

創(chuàng)建基本的觸發(fā)器：

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

結(jié)果：

INSERT INTO products VALUES('demo2','1003','xiaoguo','66.6','hello world')
> 1644 - products added
> 時(shí)間: 0.035s

解釋：

首先創(chuàng)建一個(gè)觸發(fā)器：

#newproduct 觸發(fā)器的名字
CREATE TRIGGER newproduct

觸發(fā)的時(shí)機(jī)：

BEFORE：觸發(fā)器在觸發(fā)他們的語句之前觸發(fā)

AFTER：觸發(fā)器在觸發(fā)他們的語句完成后觸發(fā)

在這里我們使用的after;也就是在插入結(jié)束后觸發(fā)條件；

DECLARE msg VARCHAR(100);

注意：declare語句是在復(fù)合語句中聲明變量的指令；如果不聲明msg,執(zhí)行語句時(shí)，MySQL報(bào)錯(cuò)；

SIGNAL SQLSTATE 'HY000' SET message_text = msg;

如果該SIGNAL語句指示特定SQLSTATE值，則該值用于表示指定的條件

"HY000”被稱為“一般錯(cuò)誤"：

如果命令出現(xiàn)一般錯(cuò)誤，則會(huì)觸發(fā)后面的message中的消息;

注：該語句只是個(gè)人理解，也是一知半解，如果有更好的解釋，歡迎留言。

觸發(fā)的條件以BEGIN開始，END結(jié)束。

觸發(fā)事件：

1. insert
2. update
3. delete
   

刪除觸發(fā)器：

-- 刪除觸發(fā)器
DROP TRIGGER newproduct;

INSERT觸發(fā)器：

insert觸發(fā)器在insert語句執(zhí)行之前或者之后執(zhí)行，需要注意以下幾點(diǎn)：

1. 在insert觸發(fā)器代碼內(nèi)?？梢砸靡粋€(gè)名為NEW的虛擬表，訪問被插入的行；
2. 在before insert觸發(fā)器中，NEW中的值也可以被更新（允許更改被插入的值）
3. 對(duì)于AUTO_INCREMENT列，NEW在insert執(zhí)行之前包含0，在insert執(zhí)行之后包含新的自動(dòng)生成值
   

例子：插入一個(gè)新的訂單時(shí)，生成一個(gè)新的訂單號(hào)保存到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;

解釋：

創(chuàng)建一個(gè)neworder的觸發(fā)器，在插入之后執(zhí)行，且對(duì)每個(gè)插入行執(zhí)行，在insert中有一個(gè)與orders表一摸一樣的虛表，用NEW 表示；

SELECT NEW.order_num into @a;

在虛表中找到我們插入的數(shù)據(jù)的編號(hào)，將標(biāo)號(hào)保存在a變量中；

檢測(cè)：

insert INTO orders(order_date,cust_id) VALUES(NOW(),10001);
SELECT @ee as num;

插入數(shù)據(jù)，輸出插入數(shù)據(jù)的編號(hào)

刪除：

drop TRIGGER neworder;

刪除觸發(fā)器。

例二：

在COURSE表上創(chuàng)建觸發(fā)器，檢查插入時(shí)是否出現(xiàn)課程名相同的記錄，若有則不操作。

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表中插入信息時(shí)，檢查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觸發(fā)器：

1. 在update觸發(fā)器的代碼中，可以引用一個(gè)名為OLD的虛擬表訪問以前的值，即：update未執(zhí)行前的值，還可以引用一個(gè)名為NEW的虛擬表訪問新更新的值；
2. 在before update觸發(fā)器中，NEW中的值可能也被更新（允許修改將要用于update語句中的值）；
3. 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:將文本轉(zhuǎn)換為大寫：

例二：不允許修改student表中的學(xué)號(hào)sno，如果修改該列則顯示錯(cuò)誤信息并取消操作。

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觸發(fā)器：

在DELETE觸發(fā)器在delete語句執(zhí)行之前或之后執(zhí)行：

1. 在delete觸發(fā)器代碼內(nèi)，可以引用OLD的虛擬表，訪問被刪除的行；
2. OLD中的值全部都是只讀，不能更新
   

例子：

使用old保存將要被刪除的行到一個(gè)存檔表中

首先先創(chuàng)建一個(gè)與orders相似的表：

CREATE TABLE archive_orders LIKE orders;

-- 創(chuàng)建一個(gè)刪除的觸發(fā)器
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表中行中信息時(shí)，將刪除的信息保存到archive_orders中；

刪除原表中一行：

DELETE FROM orders WHERE order_num='20014';

查看效果：

SELECT * FROM archive_orders;

結(jié)束：

注：如果遇到觸發(fā)器報(bào)錯(cuò)“Not allowed to return a result set from a trigger”

1. 原因：因?yàn)閺腗ySQL5以后不支持觸發(fā)器返回結(jié)果集
2. 解決方法：在后面語句后面添加 into @變量名
3. 取數(shù)據(jù)：select @變量名
   

詳細(xì)解釋：https://www.programmersought.com/article/3237975256/

創(chuàng)建用戶變量：https://www.jb51.net/article/201843.htm

到此這篇關(guān)于MySQL觸發(fā)器的使用場景及方法的文章就介紹到這了,更多相關(guān)MySQL觸發(fā)器使用內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！