MySQL trigger, mysql trigger instance

MySQL trigger, mysql trigger instance

Trigger: monitors a situation and triggers an operation.

Four elements of trigger creation Syntax: 1. monitoring location (table) 2. monitoring event (insert/update/delete) 3. trigger time (after/before) 4. trigger event (insert/update/delete)

 

~~ Syntax ~~

Create trigger <TRIGGER Name>-- The Trigger must have a name and a maximum of 64 characters. It may be followed by a separator. It is similar to the naming method of other objects in MySQL.
{BEFORE | AFTER}-- Trigger has the execution time setting: it can be set to before or after an event occurs.
{INSERT | UPDATE | DELETE}-- The trigger events can also be set: they can be triggered during the execution of insert, update, or delete.
ON <Table Name>-- A trigger belongs to a table. When an insert, update, or delete operation is performed on the table, the trigger is activated. we cannot schedule two triggers for the same event of the same table.
FOR EACHROW-- Trigger execution interval: The for each row clause notifies the trigger to execute an action on every ROW instead of the entire table.
<Trigger SQL statement>-- The trigger contains the SQL statement to be triggered: The statement here can be any legal statement, including compound statements, but the statements here are subject to the same restrictions as the functions.

 

Create trigger triggerName

After/before insert/update/delete on table name

For each row # This statement is fixed in mysql.

Begin

SQL statement;

End;

Note: Each color corresponds to the preceding four elements.

First, create two tables:

# Commodity table

Create table g

(

Id int primary key auto_increment,

Name varchar (20 ),

Num int

);

# Order table

Create table o

(

Oid int primary key auto_increment,

Gid int,

Much int

);

Insert into g (name, num) values ('item 1', 10), ('item 2', 10), ('item 3', 10 );

 

If we do not use the trigger before: Suppose we have sold 3 products 1, we need to do two things.

1. Insert a record into the Order table

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

2. Update the remaining quantity of Item 1 in the item table.

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

 

Now, create a trigger:

Execute this statement first: delimiter $ (meaning that the end of the mysql statement is changed 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 time, we only need to execute:

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

The number of Item 1 Changes to 7, which means that when we insert an order, the trigger will automatically update the order.

 

But now there is a problem, because the num and id in the trigger are all dead, so no matter which product we buy, the number of Item 1 will be updated in the end. For example, we insert another record into the Order table: insert into o (gid, much) values (2, 3). After execution, we will find that the number of Item 1 has changed to 4, the number of product 2 has not changed. This is obviously not the result we want. We need to modify the previously created trigger.

How can we reference the value of a row in the trigger, that is, we need to obtain the value of gid or much in the newly inserted order record.

For insert, the new 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 triggers like this.

Create trigger tg2
After insert on o
For each row
Begin
Update 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, delete the first trigger.

Drop trigger tg1 $

Next, let's test and insert an order record: insert into o (gid, much) values (2, 3) $

After the execution, the number of product 2 changes to 7, and now it is correct.

 

There are two cases:

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

2. How do I write a trigger change when the user modifies the number of orders?

First, let's analyze the first situation:

Monitored location: o table

Monitoring event: delete

Trigger time: after

Trigger event: update

For delete: a row is deleted later. to reference the row to be deleted, use old. The column name can reference the value of the row to be deleted.

Then we should write the trigger like this:

Create trigger tg3

After delete on o

For each row

Begin

Update g set num = num + old. much where id = old. gid; (note the changes here)

End $

The creation is complete.

Then execute delete from o where oid = 2 $

The number of product 2 is changed to 10 again.

 

Case 2:

Monitored location: o table

Monitoring event: update

Trigger time: after

Trigger event: update

For update: the modified row and the data before modification are represented by old. The old column Name Reference is the value in the row before modification;

The modified data is represented by new. The new. Column Name Reference is the value in the row after the modification.

Then we should write the trigger 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 $

Restore the old quantity first, and then subtract the new quantity, which is the modified quantity.

Let's test: Clear the data in both the product table and order table, which is easy to test.

Suppose we insert three commodities into the commodity table, and the quantity is 10,

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

At this time, the number of Item 1 becomes 7;

Modify the inserted order record: update o set much = 5 where oid = 1 $

When we purchase 5 items 1, then we can query the item table and find that there are only 5 items left in the number of item 1, which indicates that our trigger has played a role.

 

Assume that the commodity table contains Item 1 and the quantity is 10;

We insert a record into the Order table:

Insert into o (gid, much) values (1, 20 );

The number of Item 1 Changes to-10. This is the problem, because the trigger we created previously is after, that is, the trigger statement is executed after the order record is inserted, in this way, we cannot determine the number of new orders to be inserted.

 

First, let's talk about the differences between after and before:

After is used to add, delete, modify, and then trigger data. The triggered statement is later than the added, delete, and modify operations monitored, and cannot affect the previous add, delete, modify, or insert order records first, then, update the quantity of items;

Before is triggered first, and then added or deleted. The triggered statement is prior to the added, deleted, and modified monitoring statement, so we have the opportunity to determine the upcoming modification operation;

 

We use a typical case to differentiate them and create a trigger:

# Monitoring location: item table o

# Monitoring event: insert

# Trigger time: before

# Trigger event: update

Case: when an order record is added, the number of goods in the order is determined. If the number is greater than 10, the default value is 10.

Create trigger tg6

Before insert on o

For each row

Begin

If new. much> 10 then

Set new. much = 10;

End if;

Update g set num = num-new. much where id = new. gid;

End $

After the execution, delete the previously created after trigger and insert an order record:

Insert into o (gid, much) valus (1, 20) $

After the execution, you will find that the number of order records has changed to 10, and the number of Item 1 has changed to 0, so there will be no negative numbers.

 

Copyright statement: I feel very grateful if I still write well, I hope you can use your mouse and keyboard to give me a thumbs up or give me a comment! _______________________________________________________________ You are welcome to reprint. I hope to add the original address while you reprint it. Thank you for your cooperation.