sql中trigger如何使用 触发器TRIGGER的3种常见应用场景

触发器（Trigger）在数据库里算是个“幕后工作者”——平时不怎么露面，但一旦表里的数据发生变动，它就会自动跳出来执行预先设定好的任务。很多开发者会把它类比为数据库的“秘密警察”：默默监控，精准执行，不需要上层应用插手就能完成数据审计、一致性维护和复杂业务规则的落地。说它关键，一点儿也不夸张。

先看几个最常见的使用场景：数据审计与日志记录、订单系统中的库存同步、积分系统的自动增减。这些都是触发器的“拿手好戏”。下面我们就结合实际代码，把它的用法和注意事项拆开来聊一聊。

创建触发器的基本语法其实很简洁，关键就是搞清楚BEFORE/AFTER、事件类型、以及OLD和NEW这两个特殊变量的用法。

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {event}
ON table_name
FOR EACH ROW
BEGIN
    -- 触发器执行的SQL语句
END;

数据审计与日志记录：触发器在安全领域的应用

想象一下，你的数据库里存着敏感的用户信息——比如用户名、邮箱、手机号。每次有人修改这些数据，你都需要记录下改前改后是什么、谁改的、什么时候改的。如果靠应用程序一层层去写日志，不仅代码冗余，而且容易遗漏。触发器可以完美解决这个问题：在表上挂一个AFTER UPDATE触发器，它会在数据更新后自动把必要信息写入日志表。

CREATE TABLE user_info (
    id INT PRIMARY KEY,
    username VARCHAR(255),
    email VARCHAR(255),
    last_modified DATETIME
);

CREATE TABLE user_info_log (
    log_id INT PRIMARY KEY AUTO_INCREMENT,
    user_id INT,
    old_username VARCHAR(255),
    old_email VARCHAR(255),
    new_username VARCHAR(255),
    new_email VARCHAR(255),
    modified_by VARCHAR(255),
    modified_at DATETIME
);

CREATE TRIGGER user_info_audit_trigger
AFTER UPDATE
ON user_info
FOR EACH ROW
BEGIN
    INSERT INTO user_info_log (user_id, old_username, old_email, new_username, new_email, modified_by, modified_at)
    VALUES (OLD.id, OLD.username, OLD.email, NEW.username, NEW.email, USER(), NOW());
    UPDATE user_info SET last_modified = NOW() WHERE id = NEW.id;
END;

这里的关键点在于OLD和NEW两个虚行：OLD代表修改前的数据，NEW代表修改后的数据。通过它们，你能完整保留变更快照。同时别忘了用USER()和NOW()记录操作人和时间，这样审计链条就完整了。

维护数据一致性：触发器在订单系统中的角色

电商系统的订单和库存之间是一对“不能出错”的关系。用户下单必须减库存，用户取消订单必须加库存。如果靠应用代码手动处理，很可能因为并发或逻辑漏洞导致数据不一致。用触发器来做这件事，相当于把保障逻辑写进了数据库内部。

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    stock INT
);

CREATE TABLE orders (
    id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    status VARCHAR(20)  -- 例如：'pending', 'completed', 'cancelled'
);

-- 下单时减少库存
CREATE TRIGGER decrease_stock_on_order
AFTER INSERT
ON orders
FOR EACH ROW
BEGIN
    UPDATE products SET stock = stock - NEW.quantity WHERE id = NEW.product_id;
END;

-- 取消订单时增加库存
CREATE TRIGGER increase_stock_on_cancel
AFTER UPDATE
ON orders
FOR EACH ROW
BEGIN
    IF NEW.status = 'cancelled' AND OLD.status != 'cancelled' THEN
        UPDATE products SET stock = stock + OLD.quantity WHERE id = OLD.product_id;
    END IF;
END;

注意第二个触发器里的条件判断IF NEW.status = 'cancelled' AND OLD.status != 'cancelled'，这是为了防止重复触发——只有状态从未取消变成取消时，才执行库存回滚操作。这种边界处理在实际项目中非常必要。

实现复杂业务规则：触发器在积分系统中的应用

积分系统里最常见的需求：用户购买商品获得积分（比如消费金额的10%），退货时扣除相应积分。如果用应用代码来做，每个下单接口和退货接口都要调用积分更新逻辑，很容易遗漏或出错。触发器可以把这个规则固化在数据库层。

CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(255),
    points INT DEFAULT 0
);

CREATE TABLE purchases (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    returned BOOLEAN DEFAULT FALSE
);

-- 购买商品时增加积分
CREATE TRIGGER add_points_on_purchase
AFTER INSERT
ON purchases
FOR EACH ROW
BEGIN
    UPDATE users SET points = points + (NEW.amount * 0.1) WHERE id = NEW.user_id;
END;

-- 退货时扣除积分
CREATE TRIGGER deduct_points_on_return
AFTER UPDATE
ON purchases
FOR EACH ROW
BEGIN
    IF NEW.returned = TRUE AND OLD.returned = FALSE THEN
        UPDATE users SET points = points - (OLD.amount * 0.1) WHERE id = OLD.user_id;
    END IF;
END;

同样是利用OLD和NEW，以及条件判断来避免重复执行。这种设计让积分计算逻辑不再散落在各个应用服务中，而是集中在数据库层完成，大大降低了出错概率。

如何选择合适的触发时机：BEFORE vs. AFTER

BEFORE和AFTER的区别很直观：BEFORE触发器在数据实际写入之前执行，可以用来修改即将插入/更新的值，或者通过抛异常来阻止操作；AFTER触发器在数据写入之后执行，适合做日志记录、级联更新等“事后”动作。举个例子：如果你想在插入订单时自动检查库存是否足够，可以在BEFORE INSERT触发器里做校验；而记录用户修改日志，则适合用AFTER UPDATE。

触发器与存储过程的比较：何时使用哪个？

触发器和存储过程都是封装逻辑的工具，但调用方式完全不同。触发器是被动触发——事件发生了它自动跑，不需要你手动调用；存储过程则需要主动执行CALL或EXEC。所以：如果逻辑必须在数据变更时自动执行（比如审计、库存同步），选触发器；如果逻辑是用户手动触发（比如生成报表、批量处理），选存储过程。两者不是互斥关系，实际项目中经常配合使用。

触发器带来的性能影响：如何优化？

性能是触发器绕不开的问题。每次DML操作都会触发关联的触发器，如果逻辑复杂、涉及大量查询，数据库压力会明显上升。几个常见的优化方向：

• 减少触发器数量：能用应用逻辑解决的问题，尽量别用触发器堆砌。
• 简化内部逻辑：触发器里不要写太复杂的业务判断，尤其是循环和嵌套查询。
• 避免在触发器里执行复杂查询：比如关联多张大表，会严重影响峰值性能。
• 合理使用索引：触发器里访问的表，其查询条件对应的字段一定要有索引。

总的来说，触发器是数据库自动化的一把利器，但用不好也会变成性能杀手。理解它的应用场景、触发时机和限制，才能让它在项目中发挥最大价值。上面就是SQL中触发器使用的几种常见场景，希望对你有所帮助。如果你在实际项目中遇到触发器的坑，也欢迎留言讨论。