MySQL資料庫觸發器 trigger

2022-06-16 18:01:29

一、基本概念

觸發器是一種特殊型別的儲存過程，觸發器通過事件進行觸發而被執行

觸發器 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 triggersG
*************************** 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內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

MySQL資料庫觸發器 trigger

目錄

一、基本概念

1、作用

2、觸發器的優缺點

2.1、優點

2.2、缺點

二、建立觸發器

1、基本語法

2、觸發物件

3、觸發時機

4、觸發事件

5、注意事項

三、檢視觸發器

四、觸發觸發器

五、刪除觸發器

六、觸發器的應用

1、完善

2、優化

熱門文章

MySQL資料庫 觸發器 trigger

目錄

一、基本概念

1、作用

2、觸發器的優缺點

2.1、優點

2.2、缺點

二、建立觸發器

1、基本語法

2、觸發物件

3、觸發時機

4、觸發事件

5、注意事項

三、檢視觸發器

四、觸發觸發器

五、刪除觸發器

六、觸發器的應用

1、完善

2、優化

熱門文章

MySQL資料庫觸發器 trigger