Mysql之触发器

一.触发器

1.什么是触发器

• 对一张表进行 : 增、删、改操作的时候, 自动触发预先编译好的 SQL 语句的执行 (没有查操作)

2.触发器的作用

• 保证数据的完整性, 还可以起到类似于事务回滚的效果, 帮我们实现监控、日志等

3.自动触发情况

• 增前、增后
• 删前、删后
• 改前、改后

4.注意点说明

• delimiter : 改变输入的结束符，默认情况下输入结束符是分号 “;”，下面我把它改成了自定义 “%%”，这样做的目的是把多条含分号的语句做个封装，全部输入完之后一起执行，而不是一遇到默认的分号结束符就自动执行 (只在当前窗口有效)
• new : 表示即将插入的数据行
• old : 表示即将删除的数据行

5.触发器的使用

• 完整语法

delimiter %%  # 修改结束符
create  trigger [触发器名] [before/after] [insert/update/delete] on [表名]
for each row  # 表示每往上面创建的表进行指定操作之后就执行下面 "begin...end" 里面的SQL语句
begin
    [sql 语句]
end %%
delimiter ;   # 将结束符改回来

• 触发器的命名(见明知意)

🥝创建一个往"t01"表内插入记录之前触发"sql"语句的触发器
delimiter %%
create trigger tri_before_insert_t01 before insert on t01
for each row
begin
    [SQL语句]
end %%
delimiter ;

🥝创建一个在"t01"表删除记录之后触发"sql"语句的触发器
delimiter %%
create trigger tri_after_delete_t01 after delete on t01
for each row
begin
    [sql语句]
end %%
delimiter ;

🥝创建一个在对"t01"表修改数据之前就触发"sql"语句的触发器
delimiter %%
create trigger tri_before_update_t01 before update on t01
for each row
    [SQL语句]
end %%
delimiter ;

6.触发器示例

• 模拟 cmd 命令的执行, 执行失败则添加到错误日志中

🥝准备两张表"cmd"、"error_log"
create table cmd(
    id int primary key auto_increment,
    user varchar(16) not null,
    permi char(4) not null,
    cmd varchar(60) not null,
    sub_time datetime,
    success enum("yes","no") not null default "no"
);

create table error_log(
    id int primary key auto_increment,
    error_cmd varchar(60),
    error_time datetime
);

🥝创建触发器,当"cmd"表中的"cuccess"字段是"no",那么将触发执行"error_log"表的插入操作
delimiter %%
create trigger tri_after_insert_cmd after insert on cmd
for each row
begin
    if new.success="no" then  # 如果即将插入的记录的"success"字段是"no",则执行下面的语句
        insert error_log(error_cmd,error_time) value(new.cmd,new.sub_time);  # 加分号
    end if;
end %%
delimiter ;

🥝开始模拟插入记录
insert cmd(user,permi,cmd,sub_time,success) value
    ("shawn","0644","ls -l /root",now(),"yes"),
    ("shawn","0644","ps -elf",now(),"yes"),
    ("shawn","0644","groupadd xing",now(),"no"),
    ("shawn","0644","cat /etc/gshadow",now(),"no");
    
🥝查看"error_log"
select * from error_log;

7.删除触发器

drop trigger tri_after_insert_cmd

• 删除之后,再向"cmd"表中插入记录,字段"success"为"no"数据所在的行就不会再添加到"error_log"中去了