After: is the first to complete the data additions and deletions, and then trigger, trigger the statement later than the monitoring of additions and deletions, can not affect the previous additions and deletions to change the action, that is, insert the order record, and then update the product quantity. When the quantity of goods is less than the number of orders caused explosion. Before: First complete the trigger, in the change and deletion, the trigger statement before the monitoring of additions and deletions, we have the opportunity to judge, modify the upcoming operation. Case Action:Triggerusing after:Inventory is negative when the quantity of an order exceeds the quantity in stock. caused by the so-called problem of bomb-bank.
#创建触发器test4CREATE TRIGGER Test4afterinserton ' ord ' for each rowbeginupdate goods SET num= num-new.much WHERE goods_id = n Ew.gid; end$$
Triggerusing before:The first trigger, after the judgment processing and then the increase and deletion will not, according to inventory modified the maximum number of orders. Of course, this is just a simple fixed value, you can actually use the statement to get the dynamic inventory value.
#创建触发器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$$
MySQL trigger trigger after and before distinguish