The difference between MySQL trigger before and after

Let's do a test first:

The Product table G and the Order form O and the trigger are then built

Hypothesis: If the commodity table has a commodity of 1, the quantity is 10;

We insert a record into the order form:

INSERT into O (Gid,much) values (1,20);

Will find that the number of items 1 has changed to 10. This is where the problem is, because the trigger we created earlier is after, which means that the triggered statement is executed after the order is inserted, so that we cannot determine the purchase quantity of the new insert order.

Let's talk about the difference between after and before:

After is the first to complete the data additions and deletions, and then trigger, trigger the statement later than the monitoring and deletion of the operation, can not affect the previous additions and deletions to change the action, that is, first insert order records, and then update the number of goods;

Before is the first to complete the trigger, then increase the deletion, trigger the statement before the monitoring of the additions and deletions, we have the opportunity to judge, modify the impending operation;

We use a typical case to differentiate between them and create a new trigger:

#监视地点: Product Table O

#监视事件: Insert

#触发时间: Before

#触发事件: Update

Case: When a new order record is added, the number of items in the order is judged, if the quantity is greater than 10, the default is changed to 10

Create Trigger Tg6

Before insert on O

For each row

Begin

If New.much > ten Then

Set New.much = 10;

End If;

Update g Set num = Num-new.much where id = new.gid;

end$

After execution, delete the previously created after trigger and insert an order record:

INSERT into O (gid,much) valus (1,20) $

After execution, you will find that the number of order records becomes 10, and the number of items 1 becomes 0, so there will be no negative numbers.

The difference between MySQL trigger before and after