MySQL trigger learning summary

 
# Create a trigger , When the order Adding records to the table is, update 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$ 

perform
INSERT INTO `order` (gid,much) VALUES(1,5)
after
SELECT * FROM goods WHERE id=1

The number of TV sets left was found to be 30

When you perform
INSERT INTO `order` (gid,much) VALUES(2,100)
after
found
We're left with -77 refrigerators

This is an obvious loophole. How to fix it?
Since the update event occurred after insert, we were not able to pre-order the number of users (i.e. order table)
The much field) is filtered
Solutions:
When creating the trigger, change the after keyword to before and determine the number of users who placed orders


First, you have to remove the go-first trigger
drop trigger trigger1;

 
# 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$ 

Thus, when INSERT INTO 'order' (gid,much) VALUES(2,100) is executed, the number of orders written to order table is actually only 5. Similarly, the amount of inventory in goods table is only reduced by 5, because the insert operation was triggered first
The update operation allows you to judge the order quantity