MySQL trigger trigger usage (slightly complicated)

Trigger (Trigger): Monitors a situation and triggers an action.

Trigger creation Syntax four elements: 1. Watch Location (table) 2. Monitoring Event (Insert/update/delete) 3. trigger Time (After/before) 4. Trigger Event (insert/update/delete)

Grammar:

Create Trigger Triggername

After/before insert/update/delete on table name

For each row #这句话在mysql是固定的

Begin

SQL statements;

End

Note: The respective colors correspond to the four elements above.

Let's start by creating two tables:

#商品表

CREATE TABLE G

(

ID int primary KEY auto_increment,

Name varchar (20),

Num INT

);

#订单表

CREATE TABLE O

(

OID int primary Key auto_increment,

GID int,

much int

);

Insert into G (name,num) VALUES (' Commodities 1 ', 10), (' Goods 2 ', 10 '), (' Goods 3 ', 10);

If we're not using a trigger: Let's say we're selling 3 item 1, 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 commodity list item 1

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 TG1
After insert on O
For each row
Begin
Update g set num=num-3 where id=1;
end$

At this point we just execute:

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 goods 1 has changed 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 TG2
After insert on O
For each row
Begin
Update g set num=num-New.much where id=new.gid; (Note the difference between this and the first trigger)
end$

The second trigger is created, we'll first delete the first trigger.

Drop Trigger tg1$

To test again, 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 first analyze the first situation:

Watch Location: o table

Monitoring events: Delete

Trigger time: After

Trigger Event: Update

For delete: Originally there was a row, and then was deleted, want to refer to the deleted line, old to represent, old. Column names can refer to the value of the row being deleted.

Then our trigger should be written like this:

Create Trigger TG3

After delete on O

For each row

Begin

Update g Set num = num + old.much where id = old.gid; (Notice the change here)

end$

The creation is complete.

Then execute delete from o where oid = 2$

Will find that the number of goods 2 has become 10.

Second case:

Watch Location: o table

Monitoring events: Update

Trigger time: After

Trigger Event: Update

For update: The modified line, the data before the modification, the old to indicate the value of the row before the column name reference was modified;

The modified data, denoted by new, new. The column name references the value in the row after being modified.

Then our trigger should be written like this:

Create Trigger TG4

After update on O

For each row

Begin

Update g Set num = num+old.much-new.much where id = old/new.gid;

end$

Restoring the old quantity and subtracting the new quantity is the modified quantity.

Let's test it: clear the data from both the commodity and order tables, and be easy to test.

Let's say we insert three items into the product list, the quantity is 10,

Buy 3 items 1:insert into O (gid,much) VALUES (1,3) $

At this time the number of goods 1 becomes 7;

We modify the inserted order record again: Update o set much = 5 where oid = 1$

We turned to buy 5 goods 1, this time again check the commodity table will find that the number of goods 1 is only 5, indicating that our triggers play a role.

Well, I'll be here for today.

Tomorrow continue the difference between before and after?

MySQL trigger trigger usage (slightly complicated)