Detailed Explanation of Concept Principle and Usage of MySQL Trigger

This paper describes the concept, principle and usage of MySQL trigger with examples. Share it for your reference, as follows:

1. The concept of trigger

Trigger (trigger) is a method provided by MySQL 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 programs or started manually, but triggered by events. For example, when a table is operated (insert, delete, update), it will be activated for execution. -Baidu Encyclopedia

The above is the concept of trigger given by Baidu. The concept of trigger I understand is that you execute an sql statement, and the execution of this sql statement will automatically trigger the execution of other sql statements, which is as simple as that.

Ultra-simple description: sql1- > Trigger- > sqlN, one sql triggers multiple sql

2. Four elements of trigger creation

(1) Surveillance site (table)
(2) Monitoring Events (insert/update/delete)
(3) Trigger time (after/before)
(4) Trigger Events (insert/update/delete)

3. Create triggers

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

Order form: ord
Commodity list: goods

First, create the table and add a few pieces of 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 it according to the four elements created by the trigger:

Who to Monitor: ord (Order Table) Monitoring action: insert (insert operation) Trigger time: after (triggered after insertion operation) Trigger event: update (Trigger update operation)

Finally, create a 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, the monitoring ord table, for each row is finally discussed, just remember it first, and the trigger event is written between begin and end. Here is an update statement. It means that no matter what order I place, I will subtract 2 items from the inventory of item number 1.

Note: Do not run the above code yet, because the end of execution identity of mysql defaults to; . If you run the above sql statement, mysql encounters; Execution is automatically stopped, and then the end statement cannot be executed. Therefore, we need to change the end identifier of mysql to other characters first. 1 generally chooses $or $$, where $is selected as the end identifier of execution. Use the following statement to modify the end flag of MySQL execution.

delimiter $ // Settings MySQL End of execution flag, default to ;

4. View and delete existing triggers

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

5. Referencing row variables in triggers

(1) There will be a new line after insert operation on the trigger target. If the variable of this new line needs to be used in the trigger event, it can be represented by new keyword
(2) There will be an old line after delete operation on the trigger target. If the variable of this old line needs to be used in the trigger event, it can be represented by old keyword
(3) After the update operation is executed on the trigger target, the original record is the old line and the new record is the new line, so the new and old keywords can be used to operate respectively

Reduce the inventory of the corresponding goods when ordering the current order, and create triggers:

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 item 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 updating the purchase number of the order to modify the inventory of the corresponding modified item, create a trigger:

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 execute the trigger event only after the monitoring action is executed
The before operation executes the trigger event before the monitoring action is performed
There is no difference between the two triggers, but sometimes there are differences, such as:

Demand: After the user has placed an order exceeding the inventory, the order quantity of the order will be modified so that the maximum value of the order quantity is the same as the inventory quantity > Inventory, and then change the order quantity to inventory

Create triggers:

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 you use after here, you will report an error. If you use after, you will first execute insert operation, that is, insert an order, and then judge the order quantity and inventory to get a new order quantity, but you have already executed the order operation, so you will report an error. The before operation must be used here.

7. What does for each row do?

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

For example:

create trigger tn
after
update
on xxtable
for each row # Every 1 When the row is affected, the trigger event is executed, which is called row trigger 
begin
 sqlN;
end$

Execution:

update xxtable set xxx=xxx where id>100;

If the modification operation assumes 100 lines, how many times will sqlN be triggered? Answer: It will trigger 100 times.

Expand:

In oracle, if for each row is not written, no matter how many lines the update statement affects at one time, only one trigger event is executed.
For example, one person placed an order, bought 5 items, insert 5 times, and can use row-level triggers to modify the inventory 5 times; Triggered with statement-level triggers, insert1 shipping reminders.
Unfortunately, mysql does not currently support statement-level triggers.

More readers interested in MySQL can check out the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Transaction Operation Skills Encyclopedia", "MySQL Stored Procedure Skills Encyclopedia", "MySQL Database Lock Skills Encyclopedia" and "MySQL Common Functions Encyclopedia"

I hope this article is helpful to everyone's MySQL database.