MySQL trigger learning Summary

Copy codeThe Code is as follows:
# Create a trigger. When a record is added to the order table, update the goods table.
Delimiter $
Create trigger trigger1
After insert on 'order'
FOR EACH ROW
BEGIN
UPDATE goods SET num = num-new.much WHERE id = new. gid;
END $


Run
Insert into 'order' (gid, much) VALUES (1, 5)
After
SELECT * FROM goods WHERE id = 1

30 TV sets are found

When executed
Insert into 'order' (gid, much) VALUES (2,100)
After
Found
Remaining number of refrigerators-77

This is a very obvious vulnerability. How can we fix it?
Because the update event occurs after insert, we cannot place orders to users in advance (that is, the order table
).
Solution:
When creating a trigger, change the "after" keyword to "before" and determine the order quantity of users.


First, you must delete the trigger
Drop trigger trigger1;
Copy codeThe Code is as follows:
# Create a trigger
# Trigger time: before
Delimiter $
Create trigger trigger1
Before insert on 'order'
FOR EACH ROW
BEGIN
IF new. much> 5 THEN
SET new. much = 5
End if;
UPDATE goods SET num = num-new.much WHERE id = new. gid;
END $

In this way, when you execute insert into 'order' (gid, much) VALUES (2,100), the number of orders written to the order table is actually only 5. Similarly, the inventory quantity in the goods table is only reduced by 5, because it is triggered before the insert operation.
Update operation to determine the order quantity