Detailed explanation of triggers in MySQL learning summary

This article brings you relevant knowledge about mysql, which mainly introduces related issues about triggers. Triggers are a special stored procedure, triggers and stored procedures. The same is a function that can complete specific functions and storage. I hope it will be helpful to everyone.

Recommended learning: mysql tutorial

Trigger

1. Introduction to triggers

Trigger is a special stored procedure. A trigger, like a stored procedure, is a SQL segment that can complete a specific function and is stored on the database server. However, the trigger does not need to be called when performing DML operations on the data in the data table. The execution of this SQL segment is automatically triggered when

, no need to call it manually.

In MySQL, only the insert\delete\update operation can trigger the execution of the trigger.

2. Trigger usage

1.Create trigger

create trigger tri_name   -- tri_name:触发器别名
<before|after> 						-- 定义触发时机
<insert|delete|update>		-- 定义DML类型
ON <table_name>						-- 要触发的表
for each <row|statement>							
-- for each row 声明为⾏级触发器（操作的每⼀条记录就触发触发器执⾏⼀次）
-- for each statement 声明语句级触发器（触发动作体执行一次）
begin											-- 当只有一句sql语句时，begin...end 可以省略	
	sql_statement 					-- 进行触发器对应的DML类型的操作
end;


#示例
-- 创建触发器：当学⽣信息表发⽣添加操作时，则向⽇志信息表中记录⼀条⽇志
create trigger tri_test1
after insert on students
for each row
insert into stulogs(time,log_text) values(now(), concat('添 加',NEW.stu_num,'学⽣信息'));

2.View trigger

#查看全部触发器
show triggers;

#查看触发器的创建语句
show create trigger 触发器名字;

3. Delete trigger

Triggers cannot be modified, only deleted

drop trigger  触发器名字;

3. NEW and OLD

Triggers are used to monitor insert, delete, and update operations on data in the data table. Triggers usually handle

some DML related operations; we can use the NEW and OLD keywords to trigger Get the data of the DML operation that triggers this trigger

NEW: used in the trigger to get the data added by the insert operation and modified by the update operation Record

• OLD: Used in triggers to obtain the data before delete operation and data before update operation modification
• 1. Use of NEW

#insert操作中：NEW表示添加的新记录
create trigger tri_test1
after insert on students
for each row
insert into stulogs(time,log_text) values(now(), concat('添 加',NEW.stu_num,'学⽣信息'));


#update操作中：NEW 表示修改后的数据
-- 创建触发器 : 在监听update操作的触发器中，可以使⽤NEW获取修改后的数据
create trigger tri_test2
after update on students for each row
insert into stulogs(time,log_text) values(now(), concat('修改学⽣信息
为：',NEW.stu_num,NEW.stu_name));

2. Use of OLD

#delete操作中：OLD表示删除的记录
create trigger tri_test3
after delete on students for each row
insert into stulogs(time,log_text) values(now(), concat('删 除',OLD.stu_num,'学⽣信息')); 


#update操作中：OLD表示修改前的记录
create trigger tri_test2
after update on students for each row
insert into stulogs(time,log_text) values(now(), concat('将学⽣姓名从
【',OLD.stu_name,'】修改为【',NEW.stu_name,'】'));

IV. 10.4 Summary of trigger usage

1. Advantages

Triggers are automatically executed Yes, it is executed immediately when the corresponding DML operation is executed on the table related to the trigger;

• Triggers can implement cascading operations (association operations) of data in the table, which is beneficial to ensuring the integrity of the data. Integrity;
• Triggers can perform more complex legality verification on DML operation data
• 2. Disadvantages

Usage If there is a problem with the business logic implemented by the trigger, it will be difficult to locate and later maintenance will be difficult;

• Extensive use of triggers can easily lead to a messy code structure and increase the complexity of the program;
• When triggered When the amount of data operated by the processor is relatively large, the execution efficiency will be greatly reduced.
• 3. Usage suggestions

In Internet projects, you should avoid adapting triggers;

For projects with small concurrency, you can choose to use Use stored procedures, but the use of stored procedures is not recommended in Internet references

(Reason: when storing procedures, the logic of business implementation is handed over to the database for processing, which increases or decreases the load on the database; Not conducive to database migration)

Recommended learning:

mysql learning tutorial

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