MySQL database trigger

MySQL database trigger

Preface

Trigger is a database object related to the table, which refers to the set of SQL statements defined in the trigger and executed before or after insert/update/delete. This feature of triggers can assist applications in ensuring data integrity, logging, data verification and other operations on the database side.

One, the type of trigger

Use aliases OLD and NEW to refer to the changed record content in the trigger, Which is similar to other databases.Now triggers only support row-level triggers, not statement-level triggers.

Insert picture description here

Two, create a trigger

1. Grammatical structure

create trigger 触发器名称
before/after insert/update/delete
on 表名
for each row -- 代码行级触发器
begin
执行sql语句(DML)
end;

2. Example: Record the data change 1 log of the emp table through the trigger, including addition, modification, and deletion;

1. Create a log table

create table emp_logs(
id int(11) not null auto_increment primary key,
operation varchar(20) not null, -- '操作类型, insert/update/delete'
operate_time datetime not null, -- '操作时间'
operate_id int(11) not null, -- '操作表的ID'
operate_params varchar(500) -- '操作参数'
)engine=innodb default charset=utf8;

2. Create an insert trigger to complete the log record when inserting data:

delimiter $

create trigger emp_logs_insert_trigger
after insert
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'insert',now(),new.id,concat('插入后(id:',new.id,', name:',new.name,',
age:',new.age,', salary:',new.salary,')'));
end $

delimiter ;
Insert picture description here


Insert picture description here


3. Create an update trigger to complete the log record when the data is updated

delimiter $

create trigger emp_logs_update_trigger
after update
on emp
for each row
begin 
insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'update',now(),new.id,concat('修改前(id:',old.id,', name:',old.name,',
age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,',
age:',new.age,', salary:',new.salary,')'));
end$

delimiter ;
Insert picture description here


Insert picture description here


4. Create a delete row trigger, complete the log record when deleting data

delimiter $

create trigger emp_logs_delete_trigger
after delete 
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params)
values(null,'delete',now(),old.id,concat('删除前(id:',old.id,', name:',old.name,',
age:',old.age,', salary:',old.salary,')'));
end$

delimiter ;
Insert picture description here


Insert picture description here

Three, view the trigger

select * from information_schema.triggers \G;

select trigger_name from information_schema.triggers \G;
Insert picture description here

Fourth, delete the trigger

drop trigger 触发器名;
Insert picture description here