MySQL Trigger Trigger

Trigger (Trigger): Monitors a situation and triggers an action. Trigger creation Syntax four elements: 1. Monitoring location (table) 2. Monitoring Event (Insert/update/delete) 3. Trigger Time (After/before) 4. Trigger Event (Insert/update/delete) syntax: Create Trigger Triggernameafter/before insert/update/delete on table name for each row #这句话在mysql是固定的beginsql语句; end; note: the respective colors correspond to the four elements above. First we create two tables: #商品表create table g (ID int primary key auto_increment, name varchar (), num int), #订单表create table O (OID i NT PRIMARY key auto_increment, GID int, much int), insert into G (name,num) VALUES (' Goods 1 ', 10 '), (' Goods 2 ', 10 '), (' Goods 3 ', 10); If we're not using a trigger before: Suppose we sell 3 items 1 Now, we need to do two things 1. Insert a record into the order form insert into O (Gid,much) values (1,3); 2. Update the remaining quantity of item 1 in the Product List update G set num= Num-3 where id=1; Now, let's create a trigger: The statement needs to be executed first: delimiter $ (meaning to tell the end of the MySQL statement to end with $) CREATE trigger Tg1after insert on ofor each row beginupdate g SE T num=num-3 where id=1;end$ this time we just do: INSERT into O (Gid,much) VALUES (1,3) $ will find that the number of items 1 has changed to 7, indicating that when we insert an order, the trigger automatically helps us with the update operation. But now there is a problem, because we trigger that NUM and ID are written dead, so no matter which product we buy, the final update is the number of goods 1. For example: We insert a record into the order form: INSERT into O (Gid,much) values (2,3), after execution will find that the number of items 1 has changed to 4,and the number of goods 2 has not changed, which is obviously not the result we want. We need to change the trigger we created earlier. How do we refer to the value of the row in the trigger, that is, we want to get the GID or much value in our newly inserted order record. For insert, the newly inserted row is represented by new, and the value of each column in the row is represented by the new. Column name. So now we can change our trigger create TRIGGER Tg2after insert on ofor each row beginupdate g set Num=num-new.much where id=new.gid; (Note this is different from the first trigger) end$ the second trigger is created, we first delete the first trigger, drop trigger tg1$ and then test it, insert an order record: INSERT into O (Gid,much) VALUES (2,3) $ After the execution of the discovery item 2 the quantity becomes 7, now is right. Now there are two situations: 1. When the user revokes an order, we delete an order directly, do we need to add the corresponding quantity of goods back? 2. When the user modifies the quantity of an order, how do we write the trigger change? Let's analyze the first situation: monitoring location: O Watch event: Delete trigger time: After trigger event: Update for Delete: Originally a row, then deleted, If you want to refer to the deleted line, use old to indicate, old. The column name can refer to the value of the row being deleted. Then our trigger should be written like this: Create Trigger tg3after Delete on Ofor each rowbeginupdate g set num = num + old.much where id = old.gid; (Note this Side changes) end$ is created. Executing the DELETE from o where oid = 2$ will find that the number of items 2 has changed to 10. Second situation: monitoring location: O Table Monitoring event: UPDATE trigger time: After trigger event: Update for UPDATE: Modified line, modified data, old to indicate, Old. The column name refers to the value in the row before the modification, the modified data is represented by new, and the new column name refers to the value in the row after the modification. Then our trigger should be written like this: Create TRIGGER tg4after update on ofor each rowbeginupdate g set num = Num+old.much-new.muchWHERE id = old/new.gid;end$ First restores the old quantity and subtracts the new quantity from the modified quantity. Let's test it: clear the data from both the commodity and order tables, and be easy to test. Suppose we insert three items into the commodity table, the quantity is 10, buy 3 goods 1:insert into O (gid,much) VALUES (1,3) $ this time the number of goods 1 becomes 7; We modify the inserted order record: Update o set much = 5 where OID = 1$ We turn to buy 5 Item 1, this time check the commodity table will find that the number of goods 1 is only 5, indicating that our triggers play a role.

MySQL Trigger Trigger