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

软删除必须设not null default false的布尔字段，用触发器拦截delete转update，所有查询默认过滤is_deleted=false，并手动处理级联逻辑。

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

软删除字段必须是 NOT NULL 且有默认值

直接在原表加 is_deleted 字段却不设约束，后续查询漏判就会查出“已删数据”。MySQL 或 PostgreSQL 都要求该字段为 BOOLEAN 或 TINYINT(1) 类型，并显式声明 DEFAULT FALSE（或 DEFAULT 0），避免 INSERT 不带该字段时出现 NULL —— 而 NULL 在 WHERE is_deleted = FALSE 中永远不匹配，导致逻辑断裂。

ALTER TABLE users ADD COLUMN is_deleted BOOLEAN NOT NULL DEFAULT FALSE;
已有数据需同步初始化：UPDATE users SET is_deleted = FALSE WHERE is_deleted IS NULL;（执行前先确认是否存在 NULL）
严禁用 NULL 表示“未删除”——它和 FALSE 在布尔上下文里行为不一致，尤其在索引、JOIN 和部分 ORM 中会出问题

DELETE 触发器要改写为 UPDATE，且禁止 DROP/RENAME 表操作

触发器不能真正执行 DELETE，否则就不是软删除了。必须拦截 DELETE 语句，转成对 is_deleted 字段的更新。但要注意：PostgreSQL 的 BEFORE DELETE 可直接修改 NEW？不行——DELETE 没有 NEW。正确做法是 BEFORE DELETE 中执行 UPDATE 并 RETURN NULL 阻止原删除；MySQL 则用 BEFORE DELETE + UPDATE 即可，无需 RETURN。

PostgreSQL 示例：

CREATE OR REPLACE FUNCTION soft_delete_user() RETURNS TRIGGER AS $$
BEGIN
  UPDATE users SET is_deleted = TRUE WHERE id = OLD.id;
  RETURN NULL; -- 阻止实际 DELETE
END;
$$ LANGUAGE plpgsql;
<p>CREATE TRIGGER tr_soft_delete_users
BEFORE DELETE ON users
FOR EACH ROW EXECUTE FUNCTION soft_delete_user();</p>

MySQL 示例：

DELIMITER $$
CREATE TRIGGER tr_soft_delete_users
  BEFORE DELETE ON users
  FOR EACH ROW
BEGIN
  UPDATE users SET is_deleted = TRUE WHERE id = OLD.id;
END$$
DELIMITER ;

触发器无法拦截 DROP TABLE 或 TRUNCATE —— 这两类操作绕过触发器，必须靠权限管控（如回收用户 DROP 权限）或应用层禁止

所有 SELECT 必须默认过滤 is_deleted = FALSE

触发器只管删，不管查。如果业务代码还写 SELECT * FROM users，就会把软删数据一起拉出来。这不是触发器能解决的，必须统一治理入口。常见错误包括：ORM 的 raw query、报表 SQL、历史遗留脚本、备份导出语句。

图星人

一款AI图像与设计工具，主要用于好用的AI生图工具，百万免费商用图库，适合需要提升相关任务效率的用户。

下载

推荐方式：建视图封装安全查询：CREATE VIEW active_users AS SELECT * FROM users WHERE is_deleted = FALSE;，让开发只查视图
若用 ORM（如 Django），需全局重写 QuerySet 或添加 active manager；Spring Data JPA 可配 @Where(clause = "is_deleted = false")
注意：is_deleted = FALSE 比 is_deleted = 0 更具可读性，且在 PostgreSQL 中更标准；MySQL 若用 TINYINT，两者等价，但统一用 = FALSE 更少歧义