Detailed explanation of the concept, principle and usage of MySQL triggers

This article uses examples to explain the concepts, principles, and usage of MySQL triggers. Share with you for your reference, the details are as follows:

1. The concept of trigger

A trigger is a method that MySQL provides to programmers and data analysts to ensure data integrity. It is a special stored procedure related to table events. Its execution is not called by a program or started manually, but is triggered by an event. For example, it will be activated when an operation (insert, delete, update) is performed on a table. ——Baidu Encyclopedia

The above is the concept of triggers given by Baidu. My understanding of the concept of triggers is that when you execute a SQL statement, the execution of this SQL statement will automatically trigger the execution of other SQL statements. It's that simple.

Super simple description: sql1->trigger->sqlN, one sql triggers multiple sql

2. Four elements of trigger creation

(1) Monitoring location (table)
(2) Monitoring events (insert/update/delete)
(3) Trigger time (after/before)
(4) Triggering events (insert/update/delete)

3. Create a trigger

Demand: When placing an order, the inventory of the corresponding goods should be reduced accordingly, that is, the inventory should be reduced by the number of goods purchased.

Order table: ord
Goods table: goods

First, create a table and add some data:

create table goods(
  gid int,
  name varchar(20),
  num smallint
);
create table ord(
  oid int,
  gid int,
  much smallint
);
insert into goods values(1,'cat',40);
insert into goods values(2,'dog',63);
insert into goods values(3,'pig',87);

Then analyze according to the four elements of trigger creation:

• Who to monitor: ord (order table)
• Monitoring action: insert (insert operation)
• Trigger time: after (triggered after the insert operation)
• Trigger event: update (trigger update operation)

Finally create the trigger:

create trigger t1 
after
insert 
on ord
for each row
begin
 update goods set num=num-2 where gid = 1;
end$

Analysis: The name of the trigger is t1, the trigger time is after, the monitoring action is insert, monitoring the ord table, for each row will be discussed at the end, just remember it here, write the trigger event between begin and end, here is an update statement. This means that no matter what order I place, the inventory quantity of the product with product number 1 will be reduced by 2.

Note: Do not run the above code first, because the default execution end mark of MySQL is ;. If you run the above SQL statement, MySQL will automatically stop executing when it encounters ;, and the end statement will not be executed. So we need to change the end identifier of MySQL to other characters first. Generally, $ or $$ are used. Here, $ is used as the end identifier of execution. Use the following statement to modify the end mark of MySQL execution.

delimiter $ //Set the MySQL execution end mark, the default is;

4. View and delete existing triggers

(1) View existing show triggers triggers
(2) Delete an existing trigger: drop trigger triggerName

5. Reference row variables in triggers

(1) After executing the insert operation on the trigger target, a new row will be created. If the variable of the new row is needed in the trigger event, the new keyword can be used to indicate this. (2) After executing the delete operation on the trigger target, an old row will be created. If the variable of the old row is needed in the trigger event, the old keyword can be used to indicate this. (3) After executing the update operation on the trigger target, the original record is the old row and the new record is the new row. The new and old keywords can be used to operate respectively.

When an order is placed, reduce the inventory of the corresponding goods and create a trigger:

create trigger t2
after
insert 
on ord
for each row
begin
 update goods set num=num-new.much where gid=new.gid;
end$

When deleting an order, increase the inventory of the corresponding modified goods and create a trigger:

create trigger t3
after
delete
on ord
for each row
begin
 update goods set num=num+old.much where gid=old.gid;
end$

When the purchase quantity of an order is updated, the inventory quantity of the corresponding item is modified and a trigger is created:

create trigger t4
before 
update
on ord
for each row
begin
 update goods set num=num+old.much-new.much where gid = new.gid;
end$

6. The difference between after and before

The after operation is to trigger the event after the monitoring action is executed.
The before operation is to execute the trigger event before the monitoring action is executed. There is no difference between the two in general triggers, but sometimes there is a difference, such as:

Requirement: When a user places an order that exceeds the inventory, the order quantity of the order will be modified so that the maximum order quantity is the same as the inventory. Analysis: First determine if the order quantity > the inventory, then change the order quantity to the inventory.

Create a trigger:

create trigger t5
before
insert 
on ord
for each row
begin
 declare restNum int;
 select num into restNum from goods where gid = new.gid;
 if new.much > restNum then
   set new.much = restNum;
 end if;
 update goods set num=num-new.much where gid=new.gid;
end$

Note: If after is used here, an error will be reported. If after is used, the insert operation will be executed first, that is, the order operation will be inserted, and then the order quantity and inventory will be judged to obtain the new order quantity. However, the order operation has been executed, so an error will be reported. The before operation must be used here.

7. What is the purpose of for each row?

In Oracle triggers, triggers are divided into row triggers and statement triggers

for example:

create trigger tn
after
update
on xxtable
for each row #Each row is affected, and the triggering event is executed, called row trigger begin
 sqlN;
end$

implement:

update xxtable set xxx=xxx where id>100;

Assuming that the modification operation involves 100 rows, how many times will sqlN be triggered? Answer: It will be triggered 100 times.

expand:

In Oracle, if for each row is not written, no matter how many rows are affected by the update statement at a time, the trigger event will only be executed once.
For example, if a person places an order and buys five items, and inserts them five times, you can use a row-level trigger to modify the inventory five times. You can also use a statement-level trigger to insert a shipping reminder.
Unfortunately, MySQL does not currently support statement-level triggers.

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL transaction operation skills", "MySQL stored procedure skills", "MySQL database lock related skills summary" and "MySQL common function summary"

I hope this article will be helpful to everyone's MySQL database design.