触发器（trigger）
2021-04-16 22:49:30 0 举报AI智能生成
🔆MySQL数据库性能优化
作者其他创作
大纲/内容
是一种特殊类型的存储过程
不同于存储过程
主要是通过事件触发而被执行的
不是主动调用执行
存储过程需要主动调用才能执行
是指事先为某张表绑定一段代码
当表中的某些内容发生改变（增、删、改）的时候，系统会自动触发代码并执行
作用
可在写入数据前，强制检验或者转换数据(保证护数据安全)
触发器发生错误时，前面用户已经执行成功的操作会被撤销，类似事务的回滚
创建
基本语法
on 表 for each：触发对象
触发器绑定的实质是表中的所有行
因此当每一行发生指定改变时，触发器就会发生
触发时间
当 SQL 指令发生时，会令行中数据发生变化，而每张表中对应的行有两种状态
数据操作前
before：表中数据发生改变前的状态
数据操作后
after：表中数据发生改变后的状态
如果 before 触发器失败或者语句本身失败，将不执行 after 触发器(如果有的话)
触发事件
触发器是针对数据发送改变才会被触发
对应的操作只有
INSERT
insert
DELETE
delete
UPDATE
update
注意事项
在 MySQL 5 中
触发器名必须在每个表中唯一
但不是在每个数据库中唯一
即同一数据库中的两个表可能具有相同名字的触发器每个表的每个事件每次只允许一个触发器
每个表最多支持 6 个触发器，before/after insert、before/after delete、before/after update
应用
触发器针对的是数据库中的每一行记录
每行数据在操作前后都会有一个对应的状态
触发器将没有操作之前的状态保存到 old 关键字中
触发器将操作后的状态保存到 new 关键字中
语法
old/new.字段名
需要注意的是，old 和 new 不是所有触发器都有
需要记录，哪张表的哪条数据（哪个字段，定制功能）被谁在什么时候进行了什么类型的操作
同时记录 old_value 与 new_value
子主题
drop table if exists demo_tbl;
create table demo_tbl(
id int auto_increment,
info varchar(100),
primary key(id)
)engine=innodb default charset=utf8;
create table demo_tbl(
id int auto_increment,
info varchar(100),
primary key(id)
)engine=innodb default charset=utf8;
drop table if exists demo_log;
create table demo_log(
id int auto_increment comment '主键',
table_name varchar(50) comment '所涉及到的表',
data_id int comment '所涉及到的数据ID',
operation_people_id int comment '操作人',
operation_name varchar(50) comment '什么类型的操作',
operation_datetime datetime comment '操作时间',
new_value varchar(100) comment '新的值',
old_value varchar(100) comment '旧的值',
primary key(id)
)engine=innodb default charset=utf8;
create table demo_log(
id int auto_increment comment '主键',
table_name varchar(50) comment '所涉及到的表',
data_id int comment '所涉及到的数据ID',
operation_people_id int comment '操作人',
operation_name varchar(50) comment '什么类型的操作',
operation_datetime datetime comment '操作时间',
new_value varchar(100) comment '新的值',
old_value varchar(100) comment '旧的值',
primary key(id)
)engine=innodb default charset=utf8;
-- 新增数据的触发器, 当插入一条新的数据时，执行什么样的逻辑（SQL语句）
delimiter $$
drop trigger if exists after_insert_demo_tbl $$
create trigger after_insert_demo_tbl after insert on demo_tbl for each row
begin
insert into demo_log (table_name, data_id, operation_people_id, operation_name, operation_datetime, new_value, old_value) values ('demo_tbl', new.id, 1, '插入', now(), new.info, null);
end $$
delimiter ;
delimiter $$
drop trigger if exists after_insert_demo_tbl $$
create trigger after_insert_demo_tbl after insert on demo_tbl for each row
begin
insert into demo_log (table_name, data_id, operation_people_id, operation_name, operation_datetime, new_value, old_value) values ('demo_tbl', new.id, 1, '插入', now(), new.info, null);
end $$
delimiter ;
-- 测试新增触发器
insert into demo_tbl (info) values ('新增触发器测试 1');
insert into demo_tbl (info) values ('新增触发器测试 2');
insert into demo_tbl (info) values ('新增触发器测试 3');
insert into demo_tbl (info) values ('新增触发器测试 4');
-- 更新数据的触发器
delimiter $$
drop trigger if exists after_update_demo_tbl $$
create trigger after_update_demo_tbl after update on demo_tbl for each row
begin
insert into demo_log (table_name, data_id, operation_people_id, operation_name, operation_datetime, new_value, old_value) values ('demo_tbl', new.id, 1, '更新', now(), new.info, old.info);
end $$
delimiter ;
-- 测试更新的触发器
update demo_tbl set info = '更新触发器测试' where id = 3;
-- 删除数据的触发器
delimiter $$
drop trigger if exists after_delete_demo_tbl $$
create trigger after_delete_demo_tbl after delete on demo_tbl for each row
begin
insert into demo_log (table_name, data_id, operation_people_id, operation_name, operation_datetime, new_value, old_value) values ('demo_tbl', old.id, 1, '删除', now(), null, old.info);
end $$
delimiter ;
-- 测试删除数据的触发器
delete from demo_tbl where id = 4;
0 条评论
下一页
