Differentiation between after and before for MySQL trigger
After: add, delete, modify, and then trigger the data. The triggered statement is later than the added, deleted, and modified statement, which cannot affect the previous add, delete, modify, or insert order records first, then update the product quantity. Database explosion occurs when the number of products is less than the order quantity. Before. Case operation: trigger use after: when the quantity of an order exceeds the inventory quantity, the inventory is negative. This causes the so-called database explosion problem.
# Create a TRIGGER test4CREATE TRIGGER test4AFTERINSERTON 'ord 'for each rowbeginupdate goods SET num = num-new. much WHERE goods_id = new. gid; END $
Trigger using before: trigger first. After processing, add, delete, and modify the trigger. The maximum number of orders is modified based on the inventory. Of course, I just fixed a value. In fact, I can use a statement to get the dynamic inventory value.
# Create the TRIGGER test5CREATE TRIGGER test5BEFOREINSERTON 'ord 'for each rowbegin if new. much> 26 then set new. much = 26; end if; UPDATE goods SET num = num-new. much WHERE goods_id = new. gid; END $