触发器,就是一种特殊的存储过程。触发器和存储过程一样是一个能够完成特定功能、存储在数据库服务器上的SQL片段,但是触发器无需调用,当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行,无需手动条用。

  • MySQL中,只有执行insert,delete,update操作时才能触发触发器的执行

  • 触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

  • 使用别名 OLDNEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发,不支持语句级触发。

触发器的特性

  • 1、什么条件会触发:insert,delete,update

  • 2、什么时候触发:在增删改前或者后

  • 3、触发频率:针对每一行执行

  • 4、触发器定义在表上,附着在表上

创建触发器

创建只有一个执行语句的触发器:

create trigger 触发器名 before|after 触发事件
on 表名 for each row 
执行语句;

创建有多个执行语句的触发器:

create trigger 触发器名 before|after  触发事件 
on 表名 for each row
begin
     执行语句列表
end;
  • 触发事件:insert, delete, update
  • before|after :只能选一个,表示在之前或者之后
create database if not exists mydb10_trigger;
use mydb10_trigger;

## 在“用户信息操作日志表”中记录“用户表”的变动
 
-- 用户表
create table user1(
    uid int primary key ,
    username varchar(50) not null,
    password varchar(50) not null
);

-- 用户信息操作日志表
create table user1_logs(
    id int primary key auto_increment,
    time timestamp,
    log_text varchar(255)
);


-- 需求1:当user1表添加一行数据,则会在user_logs添加日志记录
-- 定义触发器:
create trigger trigger_test1 after INSERT
on user1 for each row 
insert into user1_logs values(NULL,now(),"有新用户添加");

-- 在user表中添加数据,让触发器自动执行
insert into user1 values(1,'张三','123');
insert into user1 values(2,'张四','145');
-- 运行结果:
-- 表user1中的值:
-- 1    张三  123
-- 2    张四  145
-- user1_logs
-- 1    2022-01-20 20:03:04 有新用户添加
-- 2    2022-01-20 20:03:46 有新用户添加


-- 需求2:当user表数据被修改时,则会自动在user_logs添加日志记录
delimiter $$
create trigger trigger_test2 after update on user1  -- 触发时机:当修改user表数据时触发
for each row -- 每一行
begin
        insert into user1_logs values(NULL,now(), '用户修改发生了修改');
end $$
delimiter ;

-- 在user1表中修改数据,在触发器自动执行
update user1 set password = '8888';  -- 修改所有的密码为8888

操作 NEW与OLD

MySQL 中定义了 NEW 和 OLD,用来表示触发器的所在表中,触发了触发器的那一行数据,来引用触发器中发生变化的记录内容,具体地:

触发器类型 触发器类型NEW和 OLD的使用
INSERT 型触发器 NEW 表示将要或者已经新增的数据
UPDATE 型触发器 OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据
DELETE 型触发器 OLD 表示将要或者已经删除的数据

(INSERT没有OLD)

(注意:连续定义多个触发器,后面运行一个指令,可能导致很多触发器同时启动,可以使用drop语句来删除触发器)

-- NEW和OLD

-- 需要:添加一条数据,把新增加的内容也写到日志中去
-- insert类型的触发器——NEW(没有OLD)
-- NEW 表示将要或者已经新增的数据
create trigger trigger_insert after INSERT
on user1 for each row 
insert into user1_logs values(NULL,now(),concat("有新用户添加,信息为:",NEW.uid,NEW.username,NEW.PASSWORD));

-- 给user1添加数据,触发器自动激活被启动
insert into user1 values(4,'张司','123');
--  user1里的新增数据:4   张司  123
--  user1_logs里新增的数据: 6 2022-01-21 19:11:10 有新用户添加,信息为:4张司123



-- update类型的触发器
-- OLD 表示修改之前的数据 , NEW 表示将要或已经修改后的数据

-- OLD获取改之前的值
create trigger trigger_updateold after update
on user1 for each row 
insert into user1_logs values(NULL,now(),concat("有用户信息修改,修改前为:",OLD.uid,OLD.username,OLD.PASSWORD));

update user1 set password = '99999' WHERE uid = 4;
-- 结果:
-- user1里的修改的数据:4   张司  99999
-- user1_logs里新增的数据:8   2022-01-21 19:20:12 有用户信息修改,修改前为:4张司123


-- NEW获取改之后的值
drop TRIGGER if exists trigger_updateNew;
create trigger trigger_updateNew after update
on user1 for each row 
insert into user1_logs values(NULL,now(),concat_ws(',',"有用户信息修改,修改后为:",OLD.uid,OLD.username,OLD.PASSWORD)); -- 有分割符
update user1 set password = '222222' WHERE uid = 4;
-- 结果:
-- user1里的修改的数据:4   张司  222222
-- user1_logs里新增的数据:20  2022-01-21 19:30:54 有用户信息修改,修改后为:,4,张司,222222


-- delete类型触发器
-- OLD 表示将要或者已经删除的数据
-- 只有old,没有new
drop TRIGGER if exists trigger_delete;
create trigger trigger_delete after delete
on user1 for each row 
insert into user1_logs values(NULL,now(),concat_ws(',',"有用户信息被删除,被删除的信息为:",OLD.uid,OLD.username,OLD.PASSWORD)); -- 有分割符

delete from user1 WHERE uid = 2;
-- 结果为:
-- user1里的修改的数据少一条
-- user1_logs里新增的数据:21  2022-01-21 19:35:30 有用户信息被删除,被删除的信息为:,2,张四,8888

查看触发器

show triggers;

删除触发器

-- drop trigger [if exists] trigger_name 
drop trigger if exists trigger_test1;

注意事项

  • 1.MYSQL中触发器中不能对本表进行 insert ,update ,delete 操作,以免递归循环触发

  • 2.尽量少使用触发器,假设触发器触发每次执行1s,insert table 500条数据,那么就需要触发500次触发器,光是触发器执行的时间就花费了500s,而insert 500条数据一共是1s,那么这个insert的效率就非常低了。

  • 3.触发器是针对每一行的;对增删改非常频繁的表上切记不要使用触发器,因为它会非常消耗资源。

尽量不要使用触发器或过多地使用触发器。


声明:内容来源于B站视频《2022黑马程序员最新MySQL知识精讲+mysql实战案例_零基础mysql数据库入门到高级全套教程》,博客内容仅作学习参考使用。



博主个人公众号
版权声明 ▶ 本网站名称:陶小桃Blog
▶ 本文链接:https://www.52txr.cn/2022/mysql24.html
▶ 本网站的文章部分内容可能来源于网络,仅供大家学习与参考,如有侵权,请联系站长进行核实删除。
▶ 转载本站文章需要遵守:商业转载请联系站长,非商业转载请注明出处!!

最后修改:2022 年 06 月 20 日
如果觉得我的文章对你有用,请随意赞赏