mysql手册06_触发器

触发器指在数据库 增删改前后 触发并执行的一段 SQL语句集合

触发器可以确保 数据完整性，进行 日志记录 和 数据校验

触发器使用 NEW 和 OLD 来引用触发器中发生变化的记录内容

MySQL 只支持 行级触发器 ，不支持语句级触发器

创建触发器：

通过触发器记录emp表的数据变更日志，包含增删改：

首先创建日志表：
create table emp_logs(
	id int(11) not null auto_increment,
	operation varchar(20) not null comment '操作类型',
	operate_time datetime not null comment '操作时间',
	operate_id int(11) not null comment '操作表的ID',
	operate_params varchar(500) comment '操作参数',
	primary key(id)
)engine=innodb default charset=utf8;

创建insert触发器:
create trigger emp_insert_trigger
after insert
on emp
for each row
begin
	insert into emp_logs values(null,'insert',now(),new.id,concat('插入数据（id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));
end$

测试insert：
insert into emp values(null,'光明左使',30,3500);

select * from emp_logs;
+----+-----------+---------------------+------------+--------------------------------------------------------------+
| id | operation | operate_time        | operate_id | operate_params                                               |
+----+-----------+---------------------+------------+--------------------------------------------------------------+
|  1 | insert    | 2020-08-15 15:10:02 |          5 | 插入数据（id:5, name:光明左使, age:30, salary:3500)          |
+----+-----------+---------------------+------------+--------------------------------------------------------------+

创建update触发器:
create trigger emp_update_trigger
after update
on emp
for each row
begin
	insert into emp_logs 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$

测试update：
update emp set age=39 where id=3;

select * from emp_logs;
+----+-----------+---------------------+------------+------------------------------------------------------------------------------------------------------------------------------+
| id | operation | operate_time        | operate_id | operate_params                                                                                                               |
+----+-----------+---------------------+------------+------------------------------------------------------------------------------------------------------------------------------+
|  1 | insert    | 2020-08-15 15:10:02 |          5 | 插入数据（id:5, name:光明左使, age:30, salary:3500)                                                                          |
|  2 | update    | 2020-08-15 15:19:34 |          3 | 修改前数据（id:3, name:青翼蝠王, age:38, salary:2800)修改后数据(id:3, name:青翼蝠王, age:39, salary:2800)                    |
+----+-----------+---------------------+------------+------------------------------------------------------------------------------------------------------------------------------+

创建delete触发器:
create trigger emp_delete_trigger
after delete
on emp
for each row
begin
	insert into emp_logs values(null,'delete',now(),old.id,concat('删除数据（id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));
end$

测试delete：
delete from emp where id = 5;

select * from emp_logs;
+----+-----------+---------------------+------------+------------------------------------------------------------------------------------------------------------------------------+
| id | operation | operate_time        | operate_id | operate_params                                                                                                               |
+----+-----------+---------------------+------------+------------------------------------------------------------------------------------------------------------------------------+
|  1 | insert    | 2020-08-15 15:10:02 |          5 | 插入数据（id:5, name:光明左使, age:30, salary:3500)                                                                          |
|  2 | update    | 2020-08-15 15:19:34 |          3 | 修改前数据（id:3, name:青翼蝠王, age:38, salary:2800)修改后数据(id:3, name:青翼蝠王, age:39, salary:2800)                    |
|  3 | delete    | 2020-08-15 15:24:46 |          5 | 删除数据（id:5, name:光明左使, age:30, salary:3500)                                                                          |
+----+-----------+---------------------+------------+------------------------------------------------------------------------------------------------------------------------------+

查看触发器：

show triggers\G;

*************************** 1. row ***************************
             Trigger: emp_insert_trigger
               Event: INSERT
               Table: emp
           Statement: begin
insert into emp_logs values(null,'insert',now(),new.id,concat('插入数据（id:',new.id,', name:',new.name,', age:',new.age,', salary:',new.salary,')'));
end
              Timing: AFTER
             Created: 2020-08-15 15:08:11.22
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
             Trigger: emp_update_trigger
               Event: UPDATE
               Table: emp
           Statement: begin
insert into emp_logs 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
              Timing: AFTER
             Created: 2020-08-15 15:18:29.05
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8mb4_0900_ai_ci
*************************** 3. row ***************************
             Trigger: emp_delete_trigger
               Event: DELETE
               Table: emp
           Statement: begin
insert into emp_logs values(null,'delete',now(),old.id,concat('删除数据（id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,')'));
end
              Timing: AFTER
             Created: 2020-08-15 15:23:47.56
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
             Definer: root@localhost
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8mb4_0900_ai_ci
3 rows in set (0.00 sec)

删除触发器：

drop trigger emp_delete_trigger;