触发器与事件调度¶

create trigger 触发器名
{ before | after } { insert | update | delete }
on 表名 for each row
触发器体;

delimiter $$
drop trigger if exists tr_borrow_insert$$
create trigger tr_borrow_insert
after insert on borrow
for each row
begin
    update book set state = 1 where book_id = new.book_id;
end$$
delimiter ;

insert into borrow (book_id, reader_id, borrow_date, due_date)
values (1001, 'R20240011', curdate(), date_add(curdate(), interval 30 day));

delimiter $$
drop trigger if exists tr_borrow_return$$
create trigger tr_borrow_return
after update on borrow
for each row
begin
    if old.actual_return_date is null and new.actual_return_date is not null then
        update book set state = 0 where book_id = new.book_id;
    end if;
end$$
delimiter ;

delimiter $$
drop trigger if exists tr_borrow_check_limit$$
create trigger tr_borrow_check_limit
before insert on borrow
for each row
begin
    declare v_current int;
    select count(*) into v_current
    from borrow
    where reader_id = new.reader_id and actual_return_date is null;
    if v_current >= (
        select max_borrow_limit from reader where reader_id = new.reader_id
    ) then
        signal sqlstate '45000'
            set message_text = '已达最大借阅数量，无法继续借书';
    end if;
end$$
delimiter ;

-- 查看当前库的触发器
show triggers from libraryDB;

-- 查看定义
show create trigger tr_borrow_insert;

-- 删除
drop trigger if exists tr_borrow_insert;

-- 查看是否开启（ON / OFF）
show variables like 'event_scheduler';

-- 当前会话或全局启用（需相应权限）
set global event_scheduler = on;

drop event if exists ev_daily_update_fine;

create event ev_daily_update_fine
on schedule every 1 day
starts timestamp(current_date + interval 1 day) + interval 2 hour
on completion preserve
enable
comment '每日凌晨 2 点更新借阅逾期罚金'
do
    call update_borrow_fine();

create event ev_one_time_archive
on schedule at '2026-07-01 03:00:00'
on completion not preserve
do
    delete from borrow
    where actual_return_date is not null
      and actual_return_date < date_sub(curdate(), interval 5 year);

create table if not exists borrow_weekly_stats (
    stat_date date primary key comment '统计周起始日（周一）',
    borrow_count int not null comment '当周借阅次数'
);

delimiter $$
drop event if exists ev_weekly_borrow_stats$$
create event ev_weekly_borrow_stats
on schedule every 1 week
starts (current_date - interval weekday(current_date) day) + interval 1 week + interval 8 hour
on completion preserve
enable
comment '每周一 08:00 统计上周借阅量'
do
begin
    insert into borrow_weekly_stats (stat_date, borrow_count)
    select
        curdate() - interval weekday(curdate()) day - interval 7 day,
        count(*)
    from borrow
    where borrow_date >= curdate() - interval weekday(curdate()) day - interval 7 day
      and borrow_date < curdate() - interval weekday(curdate()) day
    on duplicate key update borrow_count = values(borrow_count);
end$$
delimiter ;

-- 查看事件列表
show events from libraryDB;

-- 查看定义
show create event ev_daily_update_fine;

-- 临时禁用 / 重新启用
alter event ev_daily_update_fine disable;
alter event ev_daily_update_fine enable;

-- 修改执行计划
alter event ev_daily_update_fine
on schedule every 1 day
starts current_timestamp + interval 1 day;

-- 删除
drop event if exists ev_daily_update_fine;