MySQL Trigger (trigger)

One, the advantages of the trigger

1, the " automatic " of the trigger
To the programmer, the trigger is not visible, but he did do things, if you do not use a trigger, you update the user table name field, you also write code to update other tables in the redundant fields, I give an example, Just a table, if a few tables have redundant fields, your code is not to write a lot of it, it seems not very uncomfortable.
2, data integrity of triggers
Triggers have rollback, for example, I find that I like to lift up, that is, you want to update five tables of data, do not appear to update two tables, and the other three tables are not updated.
But if it is written in PHP Code, it is possible that this situation, such as you update the data of two tables, this time, the database is dead. You are depressed, some updates, some are not updated. This page shows inconsistencies, and has become a bug .

Second, trigger (trigger)

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

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 # this sentence is fixed in MySQL

Begin

SQL statements ;

End

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

Let's start by creating two tables:

# Product List

CREATE TABLE G

(

　　ID int primary KEY auto_increment,

　　Name varchar (20),

　　Num INT

);

# Order Form

CREATE TABLE O

(

　　OID int primary Key auto_increment,

GID int,

much int

);

Insert into G (name,num) VALUES (' commodities 1 ', "( ' product 2 ' ), (' Goods 3 ');

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 INTOO (gid,much) values (2,3), after execution, you will find that the number of goods 1 is 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 in new . A column name to represent.

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 INTOO (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 , then deleted, want to refer to the deleted line, old to represent,old. The column name 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 changed to ten .

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 is used to representthe. the column name refers to the value in the row before being modified;

The revised data is represented by new ,new. The column name reference is modified after the value in the row.

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 ten,

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 5 , Show that our triggers work.

---------------------------------------------------------------------------------------------

third, after and the before The difference:

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:

# Watch location : Product table o

# Monitoring events:Insert

# Trigger time:before

# Trigger Event:update

Case: When an order record is added, the number of items in the order is determined, if the quantity is greater than ten, the default is changed to

Create Trigger Tg6

Before insert on O

For each row

Begin

　　If New.much > ten Then

　　　　Set New.much = 10;

　　End If;

　　Update g Set num = 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 ten, and the number of items 1 becomes 0 , so there will be no negative numbers.

MySQL Trigger (trigger)