SQL 入门 14：SQL 触发器与事件：自动化数据处理

讲触发器和事件，数据库的自动化神器，表改动或定时任务都能搞定。跟着注释走，边看边整。

学习内容

1. 触发器（Trigger）
• 检查数据完整性。
• 记录操作日志（审计）。
• 自动算衍生数据。
• 强制业务规则。
• 同步其他表。
• 特殊存储过程，表有特定操作（INSERT、UPDATE、DELETE）时自动跑。
• 干啥用？
• 命名一般是 表名_after/before_操作，如 payments_after_insert。
• 用 NEW 访问新数据，OLD 访问旧数据。不能改自身表。
2. 查看与删除触发器
• SHOW TRIGGERS 列出触发器，可加 LIKE 筛选。
• DROP TRIGGER 删除，加 IF EXISTS 防报错。
3. 触发器审计
• 触发器可记录操作到审计表，存谁干了啥、啥时候干的。
4. 事件（Event）
• 定时任务，指定时间或间隔跑 SQL 或存储过程。
• 需开启 event_scheduler。
• 支持单次执行（AT）或周期执行（EVERY ... STARTS ... ENDS）。

---

示例代码与讲解

1. 创建触发器

DELIMITER $$
CREATE TRIGGER payments_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;
END $$
DELIMITER ;

• 在 payments 插入数据后，自动更新 invoices 的 payment_total，加新插入的 amount。
• AFTER INSERT 表示插入后触发，FOR EACH ROW 每行都跑。
• 用 NEW.invoice_id 和 NEW.amount 访问新数据。
• 不能改 payments 本身，不然死循环。

INSERT INTO payments
VALUES (DEFAULT, 5, 3, '2019-01-01', 888, 1);

• 插一条 payments 数据，invoices 自动同步更新。

2. 查看与删除触发器

SHOW TRIGGERS;

• 列出所有触发器。

SHOW TRIGGERS LIKE 'payments%';

• 只看 payments 相关的触发器。

DROP TRIGGER IF EXISTS payments_after_insert;

• 删触发器，IF EXISTS 防报错。

3. 触发器审计

DELIMITER $$
CREATE TRIGGER payments_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 ;

• 除了更新 invoices，还往 payments_audit 插一条记录，存谁（client_id）、啥时候（date）、多少钱（amount）、干了啥（'insert'）。

INSERT INTO payments
VALUES (DEFAULT, 5, 18, '2019-01-01', 888, 1);

• 插 payments 数据，invoices 更新，payments_audit 加一行日志。

4. 事件

SHOW VARIABLES LIKE 'event%';

• 查 event_scheduler 状态，没开就得开。

SET GLOBAL event_scheduler = ON;

• 开启事件调度。

DELIMITER $$
CREATE EVENT yearly_delete_stale_audit_rows
ON SCHEDULE
    EVERY 2 DAY STARTS '2025-04-19' ENDS '2028-08-01'
DO BEGIN
    DELETE FROM payments_audit
    WHERE action_date < NOW() - INTERVAL 1 YEAR;
END $$
DELIMITER ;

• 每 2 天跑一次，从 2025-04-19 到 2028-08-01，删 payments_audit 里超 1 年的记录。
• 也能用 AT '2025-04-19' 跑单次。

SHOW EVENTS;
SHOW EVENTS LIKE 'yearly%';

• 列出事件，或筛选 yearly 开头的。

DROP EVENT yearly_delete_stale_audit_rows;

• 删事件。

---

作业

1. 删除触发器

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 ;

• 删除 payments 数据后，减去 invoices 的 payment_total，用 OLD 访问删除的数据。

DELETE FROM payments
WHERE payment_id = 9;

• 删 payment_id = 9，触发器更新 invoices。

---

总结

讲了触发器，自动处理表改动，比如同步 invoices 或记日志到 payments_audit。还搞了事件，定时清老V3 删旧数据。用的 sql_invoicing 数据库。接下来看事务和索引。