MySQL觸發(fā)器概念、原理與用法詳解
本文實例講述了MySQL觸發(fā)器概念、原理與用法。分享給大家供大家參考,具體如下:
1、觸發(fā)器的概念
觸發(fā)器(trigger)是MySQL提供給程序員和數(shù)據(jù)分析員來保證數(shù)據(jù)完整性的一種方法,它是與表事件相關的特殊的存儲過程,它的執(zhí)行不是由程序調(diào)用,也不是手工啟動,而是由事件來觸發(fā),比如當對一個表進行操作(insert,delete, update)時就會激活它執(zhí)行?!俣劝倏?/p>
上面是百度給的觸發(fā)器的概念,我理解的觸發(fā)器的概念,就是你執(zhí)行一條sql語句,這條sql語句的執(zhí)行會自動去觸發(fā)執(zhí)行其他的sql語句,就這么簡單。
超簡說明:sql1->觸發(fā)->sqlN,一條sql觸發(fā)多個sql
2、觸發(fā)器創(chuàng)建的四個要素
(1)監(jiān)視地點(table)
(2)監(jiān)視事件(insert/update/delete)
(3)觸發(fā)時間(after/before)
(4)觸發(fā)事件(insert/update/delete)
3、創(chuàng)建觸發(fā)器
需求:在下訂單的時候,對應的商品的庫存量要相應的減少,即買幾個商品就減少多少個庫存量。
訂單表:ord
商品表:goods
首先來創(chuàng)建表并添加幾條數(shù)據(jù):
create table goods( gid int, name varchar(20), num smallint ); create table ord( oid int, gid int, much smallint ); insert into goods values(1,'cat',40); insert into goods values(2,'dog',63); insert into goods values(3,'pig',87);
然后按照觸發(fā)器創(chuàng)建的四個要素來進行分析:
- 監(jiān)視誰:ord(訂單表)
- 監(jiān)視動作:insert(插入操作)
- 觸發(fā)時間:after(在插入操作后觸發(fā))
- 觸發(fā)事件:update(觸發(fā)更新操作)
最后創(chuàng)建觸發(fā)器:
create trigger t1 after insert on ord for each row begin update goods set num=num-2 where gid = 1; end$
分析:觸發(fā)器的名稱為t1,觸發(fā)時間為after,監(jiān)視動作為insert,監(jiān)視ord表,for each row最后在進行討論,這里先記住就行了,begin和end之間寫觸發(fā)事件,這里是一個update語句。意思是不論我下什么訂單,都會把商品編號為1的商品的庫存量減去2個。
注意:先不要運行上面的代碼,因為mysql的執(zhí)行結束標識默認是;。如果運行以上的sql語句,mysql碰到;時會自動停止執(zhí)行,然后end語句就執(zhí)行不到了。所以我們需要先將mysql的結束標識符改為其他的字符,一般都選用$或者$$,這里選用$來作為執(zhí)行的結束標識。使用下面的語句來修改MySQL執(zhí)行的結束標識。
delimiter $ //設置MySQL執(zhí)行結束標志,默認為;
4、查看和刪除已有的觸發(fā)器
(1)查看已有觸發(fā)器:show triggers
(2)刪除已有觸發(fā)器:drop trigger triggerName
5、觸發(fā)器中引用行變量
(1)在觸發(fā)目標上執(zhí)行insert操作后會有一個新行,如果在觸發(fā)事件中需要用到這個新行的變量,可以用new關鍵字表示
(2)在觸發(fā)目標上執(zhí)行delete操作后會有一個舊行,如果在觸發(fā)事件中需要用到這個舊行的變量,可以用old關鍵字表示
(3)在觸發(fā)目標上執(zhí)行update操作后原紀錄是舊行,新記錄是新行,可以使用new和old關鍵字來分別操作
當下訂單時減少相應的貨品的庫存量,創(chuàng)建觸發(fā)器:
create trigger t2 after insert on ord for each row begin update goods set num=num-new.much where gid=new.gid; end$
當刪除訂單時增加相應的修改貨品的庫存量,創(chuàng)建觸發(fā)器:
create trigger t3 after delete on ord for each row begin update goods set num=num+old.much where gid=old.gid; end$
當更新訂單的購買數(shù)修改相應的修改貨品的庫存量,創(chuàng)建觸發(fā)器:
create trigger t4 before update on ord for each row begin update goods set num=num+old.much-new.much where gid = new.gid; end$
6、after和before的區(qū)別
after操作,是在執(zhí)行了監(jiān)視動作后,才會執(zhí)行觸發(fā)事件
before操作,是在執(zhí)行了監(jiān)視動作前,會執(zhí)行觸發(fā)事件
兩者在一般的觸發(fā)器中并沒有什么區(qū)別,但是有的時候有區(qū)別,如:
需求:在用戶定了超過庫存的訂單后,會修改該訂單的訂購數(shù)量,使訂購數(shù)量的最大值和庫存量相同 分析:首先判斷 訂購量 > 庫存量,然后做將訂購量改為庫存量
創(chuàng)建觸發(fā)器:
create trigger t5 before insert on ord for each row begin declare restNum int; select num into restNum from goods where gid = new.gid; if new.much > restNum then set new.much = restNum; end if; update goods set num=num-new.much where gid=new.gid; end$
注意:這里如果使用的是after就會報錯,如果使用的是after,就會先執(zhí)行insert操作,也就是插入訂單操作,然后在進行判斷下單數(shù)量和庫存量,得出新的下單數(shù)量,可是已經(jīng)執(zhí)行了下單操作了,所以就會報錯。這里必須使用before操作。
7、for each row是干什么的?
在oracle觸發(fā)器中,觸發(fā)器分為行觸發(fā)器和語句觸發(fā)器
比如:
create trigger tn after update on xxtable for each row #每一行受影響,觸發(fā)事件都執(zhí)行,叫做行觸發(fā)器 begin sqlN; end$
執(zhí)行:
update xxtable set xxx=xxx where id>100;
該修改操作假設100行,那么sqlN,會觸發(fā)多少次?答案:會觸發(fā)100次。
拓展:
在oracle中,for each row如果不寫,無論update語句一次影響了多少行,都只執(zhí)行一次觸發(fā)事件。
比如:1人下了訂單,買了5件商品,insert 5次,可以用行級觸發(fā)器,修改5次庫存;用語句級觸發(fā)器觸發(fā),insert一條發(fā)貨提醒。
遺憾的是mysql目前不支持語句級觸發(fā)器。
更多關于MySQL相關內(nèi)容感興趣的讀者可查看本站專題:《MySQL查詢技巧大全》、《MySQL事務操作技巧匯總》、《MySQL存儲過程技巧大全》、《MySQL數(shù)據(jù)庫鎖相關技巧匯總》及《MySQL常用函數(shù)大匯總》
希望本文所述對大家MySQL數(shù)據(jù)庫計有所幫助。
相關文章
MySQL?to_date()日期轉(zhuǎn)換的用法及注意事項
這篇文章主要介紹了MySQL?to_date()日期轉(zhuǎn)換的用法及注意事項,TO_DATE()函數(shù)在不同數(shù)據(jù)庫系統(tǒng)中用于將字符串轉(zhuǎn)換為日期格式,其語法和參數(shù)可能有所不同,需要的朋友可以參考下2025-01-01
出現(xiàn)錯誤mysql Table ''performance_schema...解決辦法
這篇文章主要介紹了解決出現(xiàn)錯誤mysql Table 'performance_schema.session_variables' doesn't exist的相關資料,需要的朋友可以參考下2017-04-04
MySQL實戰(zhàn)教程之Join語句執(zhí)行流程
這篇文章主要介紹了MySQL Join語句執(zhí)行流程,本文通過實例代碼給大家介紹的非常詳細,對大家的學習或工作具有一定的參考借鑒價值,需要的朋友可以參考下2023-03-03
MySQL存儲過程輸入?yún)?shù)(in),輸出參數(shù)(out),輸入輸出參數(shù)(inout)
這篇文章主要介紹了MySQL存儲過程輸入?yún)?shù)(in),輸出參數(shù)(out),輸入輸出參數(shù)(inout),存儲過程就是一組SQL語句集,功能強大,可以實現(xiàn)一些比較復雜的邏輯功能,類似于JAVA語言中的方法;Python里面的函數(shù)2022-07-07

