sql中trigger如何使用触发器TRIGGER的3种常见应用场景-SQL-PHP中文网

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

下次还敢

发布： 2025-07-19 15:09:02

原创

1130人浏览过

触发器是数据库中用于自动执行预定义操作的对象，适用于数据审计、一致性维护和复杂业务规则实现。1.在数据审计与日志记录中，触发器可自动记录用户信息修改前后的内容及时间等信息到日志表；2.在订单系统中，触发器可用于下单时减少库存或取消订单时增加库存，确保数据一致性；3.在积分系统中，触发器可在购买时增加积分或退货时扣除积分，支持复杂业务逻辑的实现；4.选择触发时机时，before适用于数据验证或修改，after适用于后续操作如日志记录；5.相比存储过程，触发器被动触发适合自动化场景，而存储过程需主动调用适合手动执行的复杂逻辑；6.使用触发器时需注意性能影响，可通过减少数量、简化逻辑、避免复杂查询和使用索引进行优化。总之，触发器是实现自动化业务逻辑的重要工具，但需要合理使用以平衡功能与性能。

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

触发器（TRIGGER）就像数据库的“秘密警察”，默默地监视着数据的变化，并在特定事件发生时自动执行一些预定义的操作。它能帮你实现一些复杂的业务逻辑，而无需修改应用程序的代码。

SQL中的触发器使用起来其实并不复杂，关键在于理解它的结构和应用场景。下面我将结合几个常见的应用场景来详细说明。

创建触发器的基本语法如下：

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;

登录后复制

这段代码创建了一个名为user_info_audit_trigger的触发器。它在user_info表发生UPDATE操作之后触发。FOR EACH ROW表示触发器会针对每一行受影响的数据执行。OLD和NEW分别代表修改前和修改后的数据。USER()函数获取当前用户，NOW()函数获取当前时间。这个触发器会将修改前后的用户名、邮箱等信息以及修改人和修改时间记录到user_info_log表中，同时更新user_info表中的last_modified字段。

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

在电商系统中，订单表和库存表是息息相关的。当用户下单时，我们需要减少库存；当用户取消订单时，我们需要增加库存。使用触发器可以保证订单和库存数据的一致性。

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;

登录后复制

这里我们创建了两个触发器。decrease_stock_on_order在orders表插入新数据（即用户下单）后，自动减少products表中的库存。increase_stock_on_cancel在orders表的状态被更新为cancelled时，自动增加products表中的库存。注意IF NEW.status = 'cancelled' AND OLD.status != 'cancelled'这个条件，确保只有当订单状态从非取消状态变为取消状态时才执行增加库存的操作。

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

假设你正在开发一个积分系统，用户每次购买商品都会获得积分，积分可以用来兑换礼品。但是，如果用户退货，我们需要扣除相应的积分。使用触发器可以方便地实现这个业务规则。

AppMall应用商店

AI应用商店，提供即时交付、按需付费的人工智能应用服务

查看详情

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;

登录后复制

add_points_on_purchase触发器在purchases表插入新数据（即用户购买商品）后，自动增加users表中的积分（假设积分是消费金额的10%）。deduct_points_on_return触发器在purchases表的returned字段被更新为TRUE时，自动扣除users表中的积分。同样，我们使用IF NEW.returned = TRUE AND OLD.returned = FALSE条件来确保只有当商品从未退货状态变为已退货状态时才执行扣除积分的操作。