MySQL: Trigger (Part II)-优快云博客

本文探讨了数据库触发器的使用，特别是Before触发器如何在执行CUD操作前进行验证，确保订单数量的合理性。通过创建触发器，实现对异常订单数量的自动调整，并展示如何列出所有触发器及验证其工作情况。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. Difference between Before and After in Trigger

1) After we perform one action, the trigger is invoked. But there is no machanism to check if the action we perform is reasonable.

# Bootstrap data
select * from goods;
+----------+------------+--------------+
| goods_id | goods_name | goods_amount |
+----------+------------+--------------+
|        1 | ASUS       |           13 |
|        2 | ThinkPad   |           10 |
+----------+------------+--------------+
select * from order_table;
+----------+----------+-------------+
| order_id | goods_id | order_count |
+----------+----------+-------------+
|        2 |        1 |           1 |
|        3 |        1 |           9 |
+----------+----------+-------------+

# Bootstrap trigger
# 1. If we place one order item, goods amount reduce.
# 2. If we delete one order item, goods amount increase.
# 3. If we modify one order item, goods amount modify.

# What if we place an order like below
insert into order_table(goods_id, order_count) values(1, 20);
select * from order_table;
+----------+----------+-------------+
| order_id | goods_id | order_count |
+----------+----------+-------------+
|        2 |        1 |           1 |
|        3 |        1 |           9 |
|        4 |        1 |          20 |
+----------+----------+-------------+
select * from goods;
+----------+------------+--------------+
| goods_id | goods_name | goods_amount |
+----------+------------+--------------+
|        1 | ASUS       |           -7 |
|        2 | ThinkPad   |           10 |
+----------+------------+--------------+

# The result is not reasonable!
delete from order_table where order_id = 4;
select * from goods;
+----------+------------+--------------+
| goods_id | goods_name | goods_amount |
+----------+------------+--------------+
|        1 | ASUS       |           13 |
|        2 | ThinkPad   |           10 |
+----------+------------+--------------+
select * from order_table;
+----------+----------+-------------+
| order_id | goods_id | order_count |
+----------+----------+-------------+
|        2 |        1 |           1 |
|        3 |        1 |           9 |
+----------+----------+-------------+

# There is lack of checking mechanism to validate whether the order item is meaningful.
# So we can use Before to validate

2) After: Once the operation that triggered this trigger, then there is no way back. The operation in trigger have to be done. The trigger is triggered after CUD works.

3) Before: Once the operation that triggered this trigger, we can make some validate/check and revocate the operation. The trigger is triggered before CUD works.

# For the order item, if order_count > 5, we suppose this order item is exception and we should modify order_count to 5;

# Four essential elements for creating Trigger
# Scope: Table order_table
# Trigger Event: Insert
# When: Before
# Triggered Event: Update

drop trigger add_order;
delimiter $
create trigger add_order
before insert on order_table
for each row
begin
    if new.order_count > 5
        then set new.order_count = 5;
   end if;
   update goods set goods_amount = goods_amount - new.order_count where goods_id = new.goods_id;
end$
delimiter ;

# Validate if the trigger works correctly
insert into order_table(goods_id, order_count) values(1, 10);
 select * from order_table;
+----------+----------+-------------+
| order_id | goods_id | order_count |
+----------+----------+-------------+
|        2 |        1 |           1 |
|        3 |        1 |           9 |
|        5 |        1 |           5 |
+----------+----------+-------------+
select * from goods;
+----------+------------+--------------+
| goods_id | goods_name | goods_amount |
+----------+------------+--------------+
|        1 | ASUS       |            8 |
|        2 | ThinkPad   |           10 |
+----------+------------+--------------+

# Bingo!

2. How to list all triggers?