如何在SQL中编写触发器防止核心数据被误删除？-SQL-PHP中文网

AFTER DELETE触发器无法拦截误删，因其在数据已物理删除后才执行，即使抛异常也仅导致事务失败而无法恢复数据；唯一可靠拦截方式是BEFORE DELETE或SQL Server的INSTEAD OF DELETE。

如何在sql中编写触发器防止核心数据被误删除？

能防，但必须用 BEFORE 或 INSTEAD OF 类型，且只对 DELETE 语句生效；TRUNCATE 和 DROP 完全绕过，得另建 DDL 触发器。

为什么 AFTER DELETE 触发器根本拦不住误删

AFTER 触发器运行时，数据已经从表中物理删除，deleted 表里虽有记录，但原行不可恢复。哪怕触发器里写 RAISERROR 或 ROLLBACK，也只是让事务失败，而“删”这个动作早已完成——你看到的是报错，实际是删完了再骂你。

常见错误现象：开发执行 DELETE FROM users 没加 WHERE，AFTER 触发器检测到后抛异常，结果日志里全是“已删除 12048 行”，错误信息在最后。

AFTER 触发器适合审计、归档、发通知，不适合防护
想拦截，唯一可靠路径是 BEFORE（MySQL/PostgreSQL）或 INSTEAD OF（SQL Server）
别在 AFTER 里写 INSERT INTO audit_log SELECT * FROM deleted 来“补救”，那是马后炮

MySQL 和 PostgreSQL 怎么用 BEFORE DELETE 阻断高危操作

MySQL 5.5+ 和 PostgreSQL 都支持在 BEFORE DELETE 中用 SIGNAL 或 RAISE EXCEPTION 主动中断事务，这是最干净的拦截方式。

PostgreSQL 示例（锁定 dim_product 表禁止无条件删除）：

CREATE OR REPLACE FUNCTION prevent_dim_delete()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'DELETE' AND NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'tg_prevent_dim_delete') THEN
    RAISE EXCEPTION '维度表 % 禁止无条件 DELETE', TG_TABLE_NAME;
  END IF;
  RETURN OLD;
END;
$$ LANGUAGE plpgsql;
<p>CREATE TRIGGER tg_prevent_dim_delete
BEFORE DELETE ON dim_product
FOR EACH ROW
EXECUTE FUNCTION prevent_dim_delete();</p>

MySQL 注意点：

必须用 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'locked'，不能用 SELECT 或 INSERT 报错
触发器内禁止修改同表数据，否则报 ERROR 1442
检查是否带 WHERE：可结合 NEW.id IS NOT NULL（ROW 级）或解析 application_name（需开启 log_bin_trust_function_creators）

SQL Server 必须用 INSTEAD OF DELETE，且逻辑要自己写全

INSTEAD OF DELETE 不会自动执行原 DELETE，而是完全替代它——这意味着你得自己决定：放行？报错？还是只删部分？漏写任何分支，数据就卡住不动。

典型错误写法：

CREATE TRIGGER tr_block_admin_delete ON users
INSTEAD OF DELETE
AS
BEGIN
  IF EXISTS (SELECT 1 FROM deleted WHERE role = 'admin')
    RAISERROR('管理员禁止删除', 16, 1);
  -- ❌ 缺少 ELSE 分支：非 admin 的记录也不会被删！
END

正确写法要显式处理放行路径：

用 deleted 表做判断，别再查基表——避免锁表和性能抖动
允许删普通用户：DELETE u FROM users u INNER JOIN deleted d ON u.id = d.id WHERE d.role != 'admin'
别在触发器里调 sp_executesql 或链接服务器，执行计划失控风险高
权限校验别放触发器里，应用层连接池常用固定账号，USER_NAME() 基本无效

TRUNCATE 和 DROP 怎么办？它们根本不走 DML 触发器

TRUNCATE TABLE 是 DDL 操作，不写完整日志、不触发任何 DML 触发器；DROP TABLE 更是对象级变更，连事务都难回滚（除非显式包在事务里）。

必须单独应对：

SQL Server：建 ON DATABASE FOR DROP_TABLE 或 FOR TRUNCATE_TABLE 的 DDL 触发器，里面写 ROLLBACK
MySQL：5.7+ 支持 CREATE TRIGGER ... BEFORE DROP ON SCHEMA，但实际依赖 performance_schema 配置，稳定性不如 SQL Server
PostgreSQL：用 EVENT TRIGGER 捕获 drop_table 事件，配合 pg_event_trigger_dropped_objects()