【MySQL】触发器trigger / 事件

发布时间:2023年12月18日

1. 触发器 trigger

  • 触发器是在插入、更新和删除语句前后执行的一堆SQL代码。
  • 使用触发器的目的:增强数据一致性;更改表1后,与表1有关联的表的相关数据也会同步更新。

1.1 触发器命名

  • 表名_after/before_操作(update/delete/insert)
  • 这样可以在查看触发器时进行筛选,可以筛选出与哪个表有关的触发器。

1.2 new和old关键字

  • new关键字:返回刚刚插入的行
  • old关键字:返回更新或删除的行

1.3 案例:insert 触发器

  • 在payments表增加新数据后,触发invoices表中相关的数据同步更新
drop trigger if exists payment_after_insert;
delimiter $$
create trigger payment_after_insert
    after insert on payments -- 还可写update / delete
    for each row -- 触发器会作用于每一个受影响的行
begin
    update invoices
        set payment_total = payment_total + new.amount
    where invoice_id = new.invoice_id;
end $$
delimiter ;

insert into payments
values (default, 5, 3, '2019-01-01', 10, 1)

结果:原来id为3的total是0,触发器后自动更新为10
在这里插入图片描述

1.4 练习:delete 触发器

  • 删除在payments表中增加的数据,invoices表中的total又变回了原来的0;
drop trigger if exists payment_after_delete;
delimiter $$
create trigger payments_after_delete
    after delete on payments
    for each row
begin
    update invoices
        set payment_total = payment_total - old.amount
    where invoice_id = old.invoice_id;
end $$
delimiter ;

delete from payments
where payment_id = 9;

结果:
在这里插入图片描述

1.5 查看触发器 show triggers

-- 查看所有触发器
show triggers
-- 按条件筛选查看触发器
show triggers like 'payments%'

在这里插入图片描述

1.6 使用触发器记录对表的操作

  • 触发器的另一常见用途:记录对表的修改和操作。当增加或删除了某条数据时,可以把操作记录下来。
  • 案例:创建了一个简易的记录表进行演示
    • 创建了一个简易的记录操作的表payments_audit来记录。会记录被操作了的数据的一些信息,以及操作本身的类型、操作时间。
-- 在插入数据后,进行操作的记录
drop trigger if exists payment_after_insert;
delimiter $$
create trigger payment_after_insert
    after insert on payments
    for each row -- 触发器会作用于每一个受影响的行
begin
    update invoices
    set payment_total = payment_total + new.amount
    where invoice_id = new.invoice_id;
    
	-- 往记录操作的表里插入一条数据,类型为插入
    insert into payments_audit
        values (new.client_id, new.date, new.amount, 'Insert', now());
end $$
delimiter ;

drop trigger if exists payments_after_delete;
delimiter $$
create trigger payments_after_delete
    after delete on payments
    for each row
begin
    update invoices
    set payment_total = payment_total - old.amount
    where invoice_id = old.invoice_id;
	-- 往记录操作的表里插入一条数据,类型为删除
    insert into payments_audit
    values (old.client_id, old.date, old.amount, 'Delete', now());
end $$
delimiter ;

insert into payments
values (default, 5, 3, '2019-01-01', 10, 1);

delete from payments
where payment_id = 10;

运行结果:payments_audit表中记录了两条操作记录
在这里插入图片描述

2 事件

  • 事件是根据计划执行的任务。
    • 事件可以执行一次,也可以按照某种规律执行。如每天早上十点或每月一次。
    • 可以通过事件来自动化数据库维护任务,如删除已经过期的数据,把数据从一张表复制到存档表,或汇总数据生成报告。

2.1 打开 / 关闭事件调度器

  • show variables like ‘event%’:找到事件管理器变量。
    • show variables:查询所有的系统变量
  • set global event_scheduler = on : 打开事件调度器
  • set global event_scheduler = off:关闭事件调度器

2.2 创建事件 create event

  • on schedule 后接时间
    • at ‘2019-05-01’,执行一次
    • every ,定期执行
  • 每隔10秒执行一次,删除执行之间超过5分钟的数据。
drop event if exists yearly_delete_stale_audit_rows;
delimiter $$
create event yearly_delete_stale_audit_rows
on schedule
    every 10 second starts '2019-01-01' ends '2029-01-01'
do begin
    delete from payments_audit
        where action_date < now() - interval 5 minute;
end $$
delimiter ;

结果:创建事件前,audit表的数据:
在这里插入图片描述
创建时间后,删除了表中的数据
在这里插入图片描述

2.3 查看,删除和更改事件

  • show events like ‘yearly%’: 查看事件
    在这里插入图片描述
  • drop event if exists yearly_delete_stale_audit_rows: 删除事件
  • alter event:修改事件,和create event同样用法。可以用alter event替换create event
    • 启用或禁用事件:
    • alter event yearly_delete_stale_audit_rows disable;
    • alter event yearly_delete_stale_audit_rows enable;
文章来源:https://blog.csdn.net/xuwuuu/article/details/134977289
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。