触发器是与表有关的数据库对象,指在insert/update/delete之前或者之后,触发并执行触发器中定义的sql语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据效验等操作。使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其他数据库相似,现在触发器还只支持行级触发,不支持语句级触发。
触发器类型 | NEW和OLD的使用 |
---|---|
INSERT型触发器 | NEW表示将要或者已经新增的数据 |
UPDATE型触发器 | OLD表示修改之前的数据,NEW表示将要或已经修改后的数据 |
DELETE型触发器 | OLD表示将要或者已经删除的数据 |
create trigger trigger_name
defore/after insert/update/delete
on tbl_name
[for each row]
trigger_stmt;
示例:通过触发器记录emp表的数据变更日志,包含增加,修改,删除;
#准备数据,emp表查看全面文章
mysql> select * from emp limit 1;
+----+--------------+------+--------+
| id | name | age | salary |
+----+--------------+------+--------+
| 1 | 金毛狮王 | 55 | 3300 |
+----+--------------+------+--------+
#建表
create table emp_logs(
id int(11) not null auto_increment,
operation varchar(20) not null comment '操作类型 , insert/update/delete',
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型触发器,完成插入时数据的日志记录
delimiter $
create trigger emp_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 ;
###测试触发器
mysql> insert into emp(id,name,age,salary) values(null,'光明',30,3000);
Query OK, 1 row affected (0.33 sec)
mysql> select * from emp_logs;
+----+-----------+---------------------+------------+-----------------------------------------------+
| id | operation | operate_time | operate_id | operate_params |
+----+-----------+---------------------+------------+-----------------------------------------------+
| 1 | insert | 2021-04-26 03:17:39 | 5 | 插入后id:5,name:光明,age:30,salary: 3000 |
+----+-----------+---------------------+------------+-----------------------------------------------+
创建update的触发器
delimiter $
create trigger emp_update_trigger
after update
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:',old.id,',name:',old.name,',age:',old.age,',salary: ',old.salary));
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 ;
####测试触发器
mysql> update emp set name='阳顶天' where name='光明';
Query OK, 1 row affected (0.36 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from emp_logs;
+----+-----------+---------------------+------------+--------------------------------------------------+
| id | operation | operate_time | operate_id | operate_params |
+----+-----------+---------------------+------------+--------------------------------------------------+
| 1 | insert | 2021-04-26 03:17:39 | 5 | 插入后id:5,name:光明,age:30,salary: 3000 |
| 2 | insert | 2021-04-26 03:21:49 | 5 | 修改前id:5,name:光明,age:30,salary: 3000 |
| 3 | insert | 2021-04-26 03:21:49 | 5 | 修改后id:5,name:阳顶天,age:30,salary: 3000 |
+----+-----------+---------------------+------------+--------------------------------------------------+
3 rows in set (0.00 sec)
mysql> show triggers\G
*************************** 1. row ***************************
Trigger: emp_insert_trigger
Event: INSERT
Table: emp
Statement: 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
Timing: AFTER
Created: 2021-04-26 03:16:09.95
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Definer: root@%
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
*************************** 2. row ***************************
Trigger: emp_update_trigger
Event: UPDATE
Table: emp
Statement: begin
insert into emp_logs(id,operation,operate_time,operate_id,operate_params) values (null,'insert',now(),new.id,concat('修改前id:',old.id,',name:',old.name,',age:',old.age,',salary: ',old.salary));
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
Timing: AFTER
Created: 2021-04-26 03:20:32.74
sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
Definer: root@%
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
Database Collation: utf8mb4_0900_ai_ci
2 rows in set (0.07 sec)
删除触发器
mysql> drop trigger emp_update_trigger;