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

6.完善

商品自動扣除庫存

觸發器針對的是資料表中的每筆記錄，每行資料再操作前後都有一個對應的狀態

觸發器在執行之前就將對應的資料狀態取得到了：

• 將沒有操作之前的資料狀態都儲存到

  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怎麼使用的詳細內容。更多資訊請關注PHP中文網其他相關文章！