MySQL數據庫 觸發器 trigger
一、基本概念
觸發器是一種特殊類型的存儲過程，觸發器通過事件進行觸發而被執行
觸發器 trigger 和js事件類似
1、作用
- 寫入數據表前，強制檢驗或轉換數據（保證數據安全）
- 觸發器發生錯誤時，異動的結果會被撤銷（事務安全）
- 部分數據庫管理系統可以針對數據定義語言DDL使用觸發器，稱為DDL觸發器
- 可以依照特定的情況，替換異動的指令 instead of（mysql不支持）
2、觸發器的優缺點
2.1、優點
- 觸發器可通過數據庫中的相關表實現級聯更改（如果一張表的數據改變，可以利用觸發器實現對其他表的操作，用戶不知道）
- 保證數據安全，進行安全校驗
2.2、缺點
- 對觸發器過分依賴，勢必影響數據庫的結構，同時增加瞭維護的復雜度
- 造成數據在程序層面不可控
二、創建觸發器
1、基本語法
create trigger 觸發器名字 觸發時機 觸發事件 on 表 for each row begin end
2、觸發對象
on 表 for each row 觸發器綁定表中所有行，沒一行發生指定改變的時候，就會觸發觸發器
3、觸發時機
每張表對應的行都有不同的狀態，當SQL指令發生的時候，都會令行中數據發生改變，每一行總會有兩種狀態：數據操作前和數據操作後
- before: 數據發生改變前的狀態
- after: 數據已經發生改變後的狀態
4、觸發事件
mysql中觸發器針對的目標是數據發生改變，對應的操作隻有寫操作（增刪改）
- inert 插入操作
- update 更新操作
- delete 刪除操作
5、註意事項
一張表中，每一個觸發時機綁定的觸發事件對應的觸發器類型隻能有一個
一張表表中隻能有一個對應的after insert 觸發器
最多隻能有6個觸發器
before insert after insert before update after update before delete after delete
需求：
下單減庫存
有兩張表，一張是商品表，一張是訂單表（保留商品ID）每次訂單生成，商品表中對應的庫存就應該發生變化
創建兩張表：
create table my_item(
id int primary key auto_increment,
name varchar(20) not null,
count int not null default 0
) comment '商品表';
create table my_order(
id int primary key auto_increment,
item_id int not null,
count int not null default 1
) comment '訂單表';
insert my_item (name, count) values ('手機', 100),('電腦', 100), ('包包', 100);
mysql> select * from my_item;
+----+--------+-------+
| id | name | count |
+----+--------+-------+
| 1 | 手機 | 100 |
| 2 | 電腦 | 100 |
| 3 | 包包 | 100 |
+----+--------+-------+
3 rows in set (0.00 sec)
mysql> select * from my_order;
Empty set (0.02 sec)
創建觸發器：
如果訂單表發生數據插入，對應的商品就應該減少庫存
delimiter $$
create trigger after_insert_order_trigger after insert on my_order for each row
begin
-- 更新商品庫存
update my_item set count = count - 1 where id = 1;
end
$$
delimiter ;
三、查看觸發器
-- 查看所有觸發器
show triggers\G
*************************** 1. row ***************************
Trigger: after_insert_order_trigger
Event: INSERT
Table: my_order
Statement: begin
update my_item set count = count - 1 where id = 1;
end
Timing: AFTER
Created: 2022-04-16 10:00:19.09
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: utf8mb4
collation_connection: utf8mb4_general_ci
Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)
-- 查看創建語句
show crate trigger 觸發器名字;
-- eg:
show create trigger after_insert_order_trigger;
四、觸發觸發器
讓觸發器執行，讓觸發器指定的表中，對應的時機發生對應的操作
insert into my_order (item_id, count) values(1, 1); mysql> select * from my_order; +----+---------+-------+ | id | item_id | count | +----+---------+-------+ | 1 | 1 | 1 | +----+---------+-------+ 1 row in set (0.00 sec) mysql> select * from my_item; +----+--------+-------+ | id | name | count | +----+--------+-------+ | 1 | 手機 | 99 | | 2 | 電腦 | 100 | | 3 | 包包 | 100 | +----+--------+-------+ 3 rows in set (0.00 sec)
五、刪除觸發器
drop trigger 觸發器名字; -- eg drop trigger after_insert_order_trigger;
六、觸發器的應用
記錄關鍵字 new old
1、完善
商品自動扣除庫存
觸發器針對的是數據表中的每條記錄，每行數據再操作前後都有一個對應的狀態
觸發器在執行之前就將對應的數據狀態獲取到瞭：
- 將沒有操作之前的數據狀態都保存到
old關鍵字中 - 操作後的狀態都放在
new中
觸發器中，可以通過old和new來獲取綁定表中對應的記錄數據
基本語法：
關鍵字.字段名
old和new並不是所有觸發器都有
- insert 插入前為空，沒有old
- delete 清除數據，沒有new
商品自動扣減庫存:
delimiter $$
create trigger after_insert_order_trigger after insert on my_order for each row
begin
-- 通過new關鍵字獲取新數據的id 和數量
update my_item set count = count - new.count where id = new.item_id;
end
$$
delimiter ;
觸發觸發器:
mysql> select * from my_order; +----+---------+-------+ | id | item_id | count | +----+---------+-------+ | 1 | 1 | 1 | +----+---------+-------+ mysql> select * from my_item; +----+--------+-------+ | id | name | count | +----+--------+-------+ | 1 | 手機 | 99 | | 2 | 電腦 | 100 | | 3 | 包包 | 100 | +----+--------+-------+ insert into my_order (item_id, count) values(2, 3); mysql> select * from my_order; +----+---------+-------+ | id | item_id | count | +----+---------+-------+ | 1 | 1 | 1 | | 2 | 2 | 3 | +----+---------+-------+ mysql> select * from my_item; +----+--------+-------+ | id | name | count | +----+--------+-------+ | 1 | 手機 | 99 | | 2 | 電腦 | 97 | | 3 | 包包 | 100 | +----+--------+-------+
2、優化
如果庫存數量沒有商品訂單多怎麼辦？
-- 刪除原有觸發器
drop trigger after_insert_order_trigger;
-- 新增判斷庫存觸發器
delimiter $$
create trigger after_insert_order_trigger after insert on my_order for each row
begin
-- 查詢庫存
select count from my_item where id = new.item_id into @count;
-- 判斷
if new.count > @count then
-- 中斷操作，暴力拋出異常
insert into xxx values ('xxx');
end if;
-- 通過new關鍵字獲取新數據的id 和數量
update my_item set count = count - new.count where id = new.item_id;
end
$$
delimiter ;
結果驗證：
mysql> insert into my_order (item_id, count) values(3, 101); ERROR 1146 (42S02): Table 'mydatabase2.xxx' doesn't exist mysql> select * from my_order; +----+---------+-------+ | id | item_id | count | +----+---------+-------+ | 1 | 1 | 1 | | 2 | 2 | 3 | +----+---------+-------+ 2 rows in set (0.00 sec) mysql> select * from my_item; +----+--------+-------+ | id | name | count | +----+--------+-------+ | 1 | 手機 | 99 | | 2 | 電腦 | 97 | | 3 | 包包 | 100 | +----+--------+-------+ 3 rows in set (0.00 sec)
到此這篇關於MySQL數據庫 觸發器 trigger的文章就介紹到這瞭,更多相關MySQL trigger內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！
推薦閱讀：
- MySQL觸發器trigger的使用
- mysql觸發器trigger實例詳解
- MySQL系列之五 視圖、存儲函數、存儲過程、觸發器
- MySQL 觸發器的使用及需要註意的地方
- MySQL數據庫的觸發器和事務