1. General

• Trigger is a special stored procedure. Like stored procedures, triggers are SQL fragments that can complete specific functions and are stored on the database server. However, triggers do not need to be called. When DML operations are performed on the data in the database table, the execution of this SQL fragment is automatically triggered without manual entry.
• In MySQL, trigger execution can only be triggered when insert, delete and update operations are performed.
• This feature of trigger can help the application in the database to ensure data integrity, logging, data verification and other operations.
• The aliases OLD and NEW are used to refer to the changed records in the trigger, which is similar to other databases. Now triggers only support row level triggering, not statement level triggering.

2. Characteristics

• What conditions trigger: I, D, U
• When to trigger: before or after adding, deleting or modifying
• Trigger frequency: executed for each line
• Triggers are defined on tables and attached to tables

3. Create trigger

① Create a trigger with only one execution statement

1 create trigger Trigger Name  before|after Trigger event
2 on Table name for each row 
3 Execute statement;

② Create multiple triggers to execute statements

1 create trigger Trigger Name  before|after  Trigger event 
2 on Table name for each row
3 begin
4      Execution statement list
5 end;

realization:

 1 -- Data preparation
 2 -- User table
 3 create table user(
 4     uid int primary key ,
 5     username varchar(50) not null,
 6     password varchar(50) not null
 7 );
 8 -- User information operation log table
 9 create table user_logs(
10     id int primary key auto_increment,
11     time timestamp,
12     log_text varchar(255)
13 );
14 
15 -- If the trigger exists, delete it first
16 drop trigger if  exists trigger_test1;
17  
18 -- Create trigger trigger_test1
19 create trigger trigger_test1
20 after insert on user -- Trigger timing: when adding user Triggered when the table data
21 for each row
22 insert into user_logs values(NULL,now(), 'New user registration');
23  
24 -- Add data, trigger automatically executes and adds log code
25 insert into user values(1,'Zhang San','123456');
26 
27 -- If trigger trigger_test2 If it exists, delete it first
28 drop trigger if exists trigger_test2;
29 
30 -- Create trigger trigger_test2
31 delimiter $$
32 create trigger trigger_test2
33 after update on user  -- Trigger timing: when modifying user Triggered when the table data
34 for each row -- Each line
35 begin
36 insert into user_logs values(NULL,now(), 'User modified');
37 end $$
38  
39 delimiter ;
40  
41 -- Add data, trigger automatically executes and adds log code
42 update user set password = '888888' where uid = 1;

4. NEW and OLD

usage method:
　　NEW.columnName (columnName is a column name of the corresponding data table)

Operation:

1 create trigger trigger_test3 after insert
2 on user for each row
3 insert into user_logs values(NULL,now(),concat('There are new users added, and the information is:',NEW.uid,NEW.username,NEW.password));
4  
5 -- test
6 insert into user values(4,'Zhao Liu','123456');

5. View triggers

show triggers;

6. Delete trigger

-- drop trigger [if exists] trigger_name 
drop trigger if exists trigger_test1;

7. Precautions

• Insert, update and delete operations cannot be performed on this table in MYSQL to avoid recursive loop triggering.
• Use as few triggers as possible. Assuming that the trigger triggers every execution for 1s and inserts 500 data in the table, you need to trigger 500 triggers. The execution time of the trigger alone takes 500s, while the total number of insert 500 data is 1s, so the efficiency of this insert is very low.
• Triggers are for each row; Remember not to use triggers on tables with frequent additions, deletions and changes, because it will consume resources.