MySQL Trigger Trigger

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

Show triggers;          View all triggers drop trigger triggername; Delete Trigger

Trigger creation syntax four elements:

1. Monitoring Location (table)

2. Monitoring Events (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是固定的//row level trigger Beginsql statement; 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 (), 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 Tg1after Insert on ofor each row beginupdate 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 Tg2after Insert on ofor each row beginupdate 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 Tg3after Delete on Ofor each rowbeginupdate 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

Listener 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 Tg4after update on ofor each rowbeginupdate 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 then modify the inserted order record:

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.

The difference between MySQL trigger before and after

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 Tg6before Insert on ofor all Rowbegin if New.much > Then set new.much = 10;　　End If; Update g Set num = Num-new.much WHERE id = new.gid;end$

Flexible application Code Case

Create Triggeer tg6before Insert on ofor each rowbegindeclare rnum int; Declare a variable select num into Rnum from G where Gid=new.gidlif New.much > rnum Then Set new.much = Rnum;end If;update g set n Um = 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.

MySQL Trigger Trigger