Detailed explanation of mysql triggers

What is a trigger:

Trigger (trigger) is a method provided by SQL server to programmers and data analysts to ensure data integrity. It is a special storage related to table events. Process, its execution is not called by a program or started manually, but is triggered by events. For example, when a table is operated (insert, delete, update), its execution will be activated. Triggers are often used to enforce data integrity constraints and business rules. Triggers can be found in the DBA_TRIGGERS and USER_TRIGGERS data dictionaries. A SQL3 trigger is a statement that can be automatically executed by the system to modify the database.

Trigger syntax:

CREATE TRIGGER --The trigger must have a name, up to 64 characters, and may be followed by a delimiter. It is the same as in MySQL The naming methods of other objects are basically similar.
{ BEFORE | AFTER } --Triggers have execution time settings: they can be set before or after the event occurs.
{ INSERT | UPDATE | DELETE } --You can also set the triggered events: they can be triggered during the execution of insert, update or delete.
ON

--The trigger belongs to a certain table: when an insert, update, or delete operation is performed on this table, the trigger is activated. We cannot give the trigger to the same table. An event schedules two triggers.
FOR EACH ROW --Trigger execution interval: The FOR EACH ROW clause notifies the trigger to perform an action every other row, rather than once for the entire table.
--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 functions.

A table can only create one trigger

Trigger (trigger): monitor a certain situation and trigger a certain operation.

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

Syntax:

create trigger triggerName
after/before insert/update/delete on 表名
for each row   #这句话在mysql是固定的
begin

sql statement;

end;

Note: Each color corresponds to the four elements above.

First we create 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('商品1',10),('商品2',10),('商品3',10);

If we don’t use triggers: Suppose we sell 3 items 1 now, 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 product 1 in the product table

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

Now, let’s create a trigger:

Required First execute the statement: delimiter $ (meaning to tell 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 time we only need to execute:

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

will find that the quantity of product 1 becomes 7 , indicating that when we insert an order, the trigger automatically performs the update operation for us.

But now there is a problem, because the num and id in our trigger are hard-coded, so no matter which product we buy, the quantity of product 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 quantity of product 1 has changed to 4, but the quantity of product 2 has not changed. This is obviously not the case. the results 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 to say, we need 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 new.column name.

So now we can change our trigger like this

create trigger tg2
after insert on o
for each row 
begin
update g set num=num-new.much where id=new.gid;(注意此处和第一个触发器的不同)
end$

The second trigger is created, let’s delete the first trigger first

drop trigger tg1$

Let’s test it again and insert an order record: insert into o(gid,much) values(2,3)$

After execution, it is found that the quantity of product 2 has changed to 7. That's right now.

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? ?

2. When the user modifies the quantity of an order, how do we write the trigger modification?

Let’s analyze the first situation first:

Monitoring location: o table

Monitoring event: delete

Trigger time: after

Trigger event: update

For delete: There was originally a row, but it was deleted later. To reference the deleted row, use old to represent it. The old. column name can refer to the value of the deleted row.

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;(注意这边的变化)
end$

Created.

Execute delete from o where oid = 2$

and you will find that the quantity of product 2 has changed to 10 again.

Second case:

Monitoring location: o table

Monitoring event: update

Trigger time: after

Trigger event :update

For update: The modified row, the data before modification, is represented by old, and the old. column name refers to the value in the row before modification;

After modification The data is represented by new, and the new. column name refers to the value in the row after it is 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$

First restore the old quantity and then subtract the new quantity to get the modified quantity.

Let’s test it: first clear the data in the product table and order table for easy testing.

Suppose we insert three products into the product table, the quantity is 10,

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

At this time, the quantity of product 1 becomes 7;

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

We change it to buy 5 Product 1. If you query the product table at this time, you will find that the number of Product 1 is only 5, indicating that our trigger has worked.

The above is all about the triggers in this article, I hope it can help you.

Related recommendations:

Questions about MySQL triggers

Detailed explanation of indexes and triggers in MySQL

Detailed explanation of operation examples of triggers in Mysql

The above is the detailed content of Detailed explanation of mysql triggers. For more information, please follow other related articles on the PHP Chinese website!