## mysql之觸發器trigger
觸發器(trigger):監視某種情況,并觸發某種操作。
觸發器創建語法四要素:
1. 監視地點(table)
2. 監視事件(insert/update/delete)
3. 觸發時間(after/before)
4. 觸發事件(insert/update/delete)
語法:
~~~
create trigger triggerName
after/before insert/update/delete on 表名
for each row #這句話在mysql是固定的
begin
sql語句;
end;
~~~
注:各自顏色對應上面的四要素。
首先我們來創建兩張表:
~~~
#商品表
create table g
(
id int primary key auto_increment,
name varchar(20),
num int
);
~~~
~~~
#訂單表
CREATE TABLE o
(
oid int primary key auto_increment,
gid int,
much int
);
INSERT INTO g(name,num) VALUES
('商品1',10),
('商品2',10),
('商品3',10);
~~~
如果沒有使用觸發器:假設我們賣了3個商品1的時候,我們需要做兩件事
1 . 往訂單表插入一條記錄
~~~
insert into o(gid,much) values(1,3);
~~~
2 . 更新商品表商品1的剩余數量
~~~
update g set num=num-3 where id=1;
~~~
現在,我們來創建一個觸發器:
需要先執行該語句:
~~~
delimiter $ (意思是告訴mysql語句的結尾換成以$結束)
create trigger tg1
after insert on o
for each row
begin
update g set num=num-3 where id=1;
end$
~~~
這時候我們只要執行:
~~~
insert into o(gid,much) values(1,3)$
~~~
就會發現商品1的數量變為7了,說明在我們插入一條訂單的時候,觸發器自動幫助我們做了更新的操作。
但是現在會有一個問題,因為我們觸發器里面num和id都是寫死的,所以不管我們買哪個商品,最終更新的都是商品1的數量。
比如:我們往訂單表再插入一條記錄:insert into o(gid,much) values(2,3),執行完后會發現商品1的數量變4了,而商品2的數量沒變,這顯然不是我們想要的結果。
需要改改我們之前創建的觸發器。如何在觸發器引用行的值?也就是說我們要得到我們新插入的訂單記錄中的gid或much的值。對于insert而言,新插入的行用new來表示,行中的每一列的值用new.列名來表示。
所以現在我們可以這樣來改我們的觸發器
~~~
create trigger tg2
after insert on o
for each row
begin
update g set num=num-new.much where id=new.gid;(注意此處和第一個觸發器的不同)
end$
~~~
第二個觸發器創建完畢,我們先把第一個觸發器刪掉
~~~
drop trigger tg1$
~~~
再來測試一下,插入一條訂單記錄:
~~~
insert into o(gid,much) values(2,3)$
~~~
執行完發現商品2的數量變為7了,現在就對了。
現在還存在兩種情況:
1.當用戶撤銷一個訂單的時候,刪除一個訂單,我們是需要把對應的總商品數量再加回去?
2.當用戶修改一個訂單的數量時,我們觸發器修改總商品數量的邏輯應該怎么寫?
我們先分析一下第一種情況:
~~~
監視地點:o表
監視事件:delete
觸發時間:after
觸發事件:update
~~~
對于delete而言:原本有一行,后來被刪除,想引用被刪除的這一行,用old來表示,old.列名可以引用被刪除的行的值。
我們的觸發器應該這樣寫:
~~~
create trigger tg3
after delete on o
for each row
begin
update g set num = num + old.much where id = old.gid;(注意這邊的變化)
end$
~~~
創建完畢。再執行
~~~
delete from o where oid = 2$
~~~
會發現商品2的數量又變為10了。
第二種情況:
~~~
監視地點:o表
監視事件:update
觸發時間:after
觸發事件:update
~~~
對于update而言:被修改的行,修改前的數據,用old來表示,old.列名引用被修改之前行中的值;
修改的后的數據,用new來表示,new.列名引用被修改之后行中的值。
那我們的觸發器就該這樣寫:
~~~
create trigger tg4
after update on o
for each row
begin
update g set num = num+old.much-new.much where id = old/new.gid;
end$
~~~
先把舊的數量恢復再減去新的數量就是修改后的數量了。
測試一下:先把商品表和訂單表的數據都清掉。假設我們往商品表插入三個商品,數量都是10,買3個商品1:
~~~
insert into o(gid,much) values(1,3)$
~~~
這時候商品1的數量變為7;
我們再修改插入的訂單記錄:
~~~
update o set much = 5 where oid = 1$
~~~
我們變為買5個商品1,這時候再查詢商品表就會發現商品1的數量只剩5了,說明我們的觸發器發揮作用了。
- 授權管理
- 角色管理
- 設置密碼
- 5.6 版本
- 系統用戶
- 當前用戶
- 目錄
- 設計規劃
- 數據字典
- 狀態監控
- 查看MYSQL表占用空間狀態
- show table status
- SHOW 命令
- SHOW TABLE STATUS
- 表格輸出
- 調優
- 書籍培訓
- 數據庫管理員的第一本書(原書第2版)
- 視頻
- 收獲,不止SQL優化
- 基本概念
- 工具
- phpMyadmin
- 變更管理
- 數據關系與原則
- 數據完整性
- 業務完整性
- 字段更新(1)
- 訂單應用(1)
- 訂單應用(2)
- 表間數據連接
- 數據管理
- Cheet Sheet
- Database Administrator
- 索引設計
- Mysql 四種常見的索引
- MySQL索引之主鍵索引
- MySQL索引使用對查詢、插入速度的影響
- 查詢優化
- 存儲優化
- 分割數據表字段
- Procedure_Analyse優化表結構
- 性能優化
- 拆分DELETE/INSERT語句
- MySQL命令
- 表復制
- 如何快速創建相同結構的表
- 主鍵設計
- 為什么推薦InnoDB引擎使用自增主鍵?
- INFORMATION_SCHEMA
- _5.6版本
- USER_PRIVILEGES