mysql 触发器

访问量:1631 创建时间:2021-04-26

触发器概述

触发器是与表有关的数据库对象，指在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;

<< 上一章节

下一章节 >>

登陆评论: 使用GITHUB登陆