MySQL: Trigger (Part II)

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?

# Show all triggers
show triggers;