如何使用SQL触发器实现软删除-SQL-PHP中文网

软删除不能直接用delete语句，因其会物理移除数据、丢失审计线索并破坏外键级联；必须用before delete触发器拦截并转为update，通过deleted_at或is_deleted字段标记状态，且where条件须用old.id、返回null（postgresql）或等效机制阻止原删除。

如何使用sql触发器实现软删除

软删除为什么不能直接用 DELETE 语句

因为 DELETE 是物理移除，无法回溯、无法保留关联审计线索、会破坏外键约束下的级联逻辑。软删除本质是“标记删除”，靠字段（如 is_deleted 或 deleted_at）控制数据可见性，但业务代码容易漏判、重复加条件，触发器能强制拦截原始 DELETE 操作并转为更新。

CREATE TRIGGER 必须用 BEFORE DELETE 而非 AFTER

AFTER DELETE 触发时行已被删，没法再改；BEFORE DELETE 可在删除动作发生前截获、取消物理删除，并执行自定义逻辑。PostgreSQL 和 MySQL 都支持，但语法细节不同：

MySQL 要求触发器名唯一，且必须指定表名和事件（BEFORE DELETE ON users）
PostgreSQL 允许函数封装逻辑，触发器只负责调用，更易复用
SQL Server 使用 INSTEAD OF DELETE，语义等价于 BEFORE，但写法不同

示例（PostgreSQL）：

CREATE OR REPLACE FUNCTION soft_delete_user() RETURNS TRIGGER AS $$
BEGIN
  UPDATE users SET deleted_at = NOW(), is_deleted = true WHERE id = OLD.id;
  RETURN NULL; -- 阻止原 DELETE 执行
END;
$$ LANGUAGE plpgsql;

WHERE 条件必须用 OLD.id 而非 NEW.id

DELETE 没有 NEW 行，只有 OLD —— 即将被删的那行旧数据。用 NEW.id 会报错或更新错行。常见错误包括：

误写成 UPDATE ... WHERE id = NEW.id → PostgreSQL 报 column "new" does not exist
没加 RETURN NULL（PostgreSQL）或 SET NEW.id = NULL（MySQL）→ 触发器执行完仍会继续物理删除
未处理多行删除（如 DELETE FROM users WHERE status = 'inactive'）→ 触发器对每一行分别触发，OLD 是单行，逻辑依然成立

索引和查询性能隐患要提前加

软删除后，WHERE is_deleted = false 成为高频过滤条件，但默认索引不包含该字段。不加复合索引会导致全表扫描：

对主查询字段 + is_deleted 建复合索引，例如 CREATE INDEX idx_users_active ON users (tenant_id, is_deleted) WHERE is_deleted = false（PostgreSQL 的部分索引更高效）
避免在 WHERE 中写 is_deleted != true，某些 ORM 生成的 SQL 可能绕过索引；统一用 is_deleted = false
触发器本身不锁表，但 UPDATE 操作会加行级锁，高并发批量删需注意锁等待

真正麻烦的是历史代码——所有已有 SELECT * FROM users 都得补 WHERE is_deleted = false，触发器只拦删，不拦查。