Shulou(Shulou.com)06/01 Report--

The following together to understand what the role of mysql triggers, I believe that we will benefit a lot after reading, the text in the essence is not much, hope that mysql triggers have the role of this short content is what you want.

Trigger syntax:

CREATE TRIGGER-the trigger must have a name, up to 64 characters, and may be followed by a delimiter. It is basically similar to the naming of other objects in MySQL.

{BEFORE | AFTER}-the trigger has a time setting for execution: it can be set before or after the event.

{INSERT | UPDATE | DELETE}-you can also set triggered events: they can be triggered during the execution of insert, update, or delete.

ON-A trigger belongs to a table: when an insert, update, or delete operation is performed on this table, the trigger is activated. We cannot schedule two triggers for the same event in the same table.

FOR EACH ROW-the interval at which the trigger executes: the FOR EACH ROW clause tells the trigger to perform an action every other row, rather than on the entire table.

-- the trigger contains the SQL statement to be triggered: the statement here can be any legal statement, including the compound statement, but the statement here is subject to the same restrictions as the function.

Only one trigger can be created for a table

Trigger: monitors a situation and triggers an action.

Trigger creation syntax four elements: 1. Surveillance location (table) 2. Monitoring events (insert/update/delete) 3. Trigger time (after/before) 4. Trigger event (insert/update/delete)

Syntax:

The sentence create trigger triggerNameafter/before insert/update/delete on table name for each row # is a fixed begin in mysql

Sql statement

End

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

First, let's create two tables:

# Commodity 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 ('Commodity 1) (10), (' Commodity 2) (10), ('Commodity 3) (10)

If we do not use the trigger: suppose we now sell three goods 1, we need to do two things

1. Insert a record into the order table

Insert into o (gid,much) values (1BI 3)

two。 Update the remaining quantity of item 1 in the commodity list

Update g set num=num-3 where id=1

Now, let's create a trigger:

You need to execute this statement first: delimiter $(which means telling mysql to end the statement with $)

Create trigger tg1after insert on ofor each row beginupdate g set num=num-3 where id=1;end$

At this point, all we have to do is:

Insert into o (gid,much) values (1BI 3) $

You will find that the number of item 1 has changed to 7, which means that when we insert an order, the trigger automatically updates for us.

But now there will be a problem, because both num and id are written dead in our triggers, so no matter which item we buy, the final update is the number of item 1. For example, if we insert another record into the order table: insert into o (gid,much) values (2p3), after execution, we will find that the quantity of item 1 has changed to 4, while the quantity of item 2 has not changed, which is obviously not the result we want. We need to change the trigger we created earlier.

How do we reference the value of the row in the trigger, that is, we need to get the value of gid or much 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 new. Column name to indicate.

So now we can change our triggers like this.

Create trigger tg2after insert on ofor each row beginupdate g set num=num-new.much where id=new.gid; (note that this is different from the first trigger) end$

After the second trigger is created, let's delete the first trigger.

Drop trigger tg1 $

To test it again, insert an order record: insert into o (gid,much) values (2p3) $

After the execution, it is found that the number of goods 2 has changed to 7, and now it is right.

There are still two situations:

1. When a user cancels an order, we delete an order directly. Do we need to add back the corresponding quantity of goods?

two。 When a user modifies the quantity of an order, how do we change the trigger?

Let's first analyze the first situation:

Monitoring location: O table

Monitoring events: delete

Trigger time: after

Trigger event: update

For delete: there was originally a line, but then it was deleted, and I want to refer to the deleted line, which is represented by old, old. The column name can refer to the value of the deleted row.

Then our trigger should say:

Create trigger tg3after delete on ofor each rowbeginupdate g set num = num + old.much where id = old.gid; (note the change here) end$

Created.

Then execute delete from o where oid = 2 $

You will find that the number of commodity 2 has changed to 10 again.

The second situation:

Monitoring location: O table

Monitoring events: update

Trigger time: after

Trigger event: update

For update: modified rows, pre-modified data, represented by old, old. The column name refers to the value in the row before it was modified

The modified data is represented by new, new. The column name refers to the value in the row after the modification.

Then our trigger should say:

Create trigger tg4after update on ofor each rowbeginupdate g set num = num+old.much-new.much where id = old/new.gid;end$

First restore the old quantity and then subtract the new quantity is the revised quantity.

Let's test it: first, clear out the data of the goods table and the order table, so it is easy to test.

Suppose we insert three items into the list of goods, all of which are 10.

Buy 3 items 1:insert into o (gid,much) values (1p3) $

At this time, the quantity of commodity 1 becomes 7.

Let's modify the inserted order record: update o set much = 5 where oid = 1 $

We change to buy 5 goods 1, at this time check the commodity table, we will find that the quantity of item 1 is only 5, indicating that our trigger is working.

After reading this article on the role of mysql triggers, many readers will want to know more about it. For more industry information, you can follow our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.