如何使用SQL触发器限制高风险删除操作-SQL-PHP中文网

mysql的before delete触发器必须用signal sqlstate '45000'才能真正中断删除，仅if判断加select 1/0等操作无效；truncate和drop不触发行级触发器，须靠权限控制兜底。

如何使用sql触发器限制高风险删除操作

MySQL 的 BEFORE DELETE 必须用 SIGNAL 才能真正中断

只写 IF OLD.is_protected = 1 THEN SELECT 1/0; END IF; 不会阻止删除——MySQL 把它当“顺手执行的语句”，不视为失败。真正生效的只有 SIGNAL SQLSTATE '45000'，且状态码必须是 5 位字符串（如 '45000' 是通用自定义错误标准）。

错误消息要带上下文，比如 MESSAGE_TEXT = 'Cannot delete system user id=1'，方便日志归类和排查；别在触发器里声明 HANDLER 或 DECLARE CONTINUE HANDLER，纯属冗余。

SIGNAL 必须放在 BEFORE DELETE 中，AFTER 触发时数据已删完
不能依赖 RETURN 或空 BEGIN END 块，它们对 MySQL 触发器无效
避免在触发器中查大表或调用函数，否则每次 DELETE 都会变慢甚至锁表

SQL Server 要用 INSTEAD OF DELETE，不是 AFTER

AFTER DELETE 触发器运行时行已经删了，抛错也救不回来；INSTEAD OF DELETE 才是真正可拦截的时机——它不会自动执行原操作，你得自己决定“删还是不删”。

一个空的 BEGIN END 就能阻止所有 DELETE；若需放行部分数据，必须显式 DELETE 并关联 deleted 表，例如：DELETE u FROM users u INNER JOIN deleted d ON u.id = d.id WHERE d.role != 'admin'。

严禁在触发器内再写 DELETE FROM users，会递归触发自身，报错 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
含 ON DELETE CASCADE 外键的表不支持建 INSTEAD OF DELETE 触发器，建表时就会报错 Msg 3724
仅靠 RAISERROR 不会自动回滚事务，必须搭配 ROLLBACK 才能真正拦截

PostgreSQL 需 RAISE EXCEPTION，RETURN NULL 不够

只写 RAISE NOTICE 或函数末尾 RETURN NULL 不会阻止删除——前者只是打日志，后者在触发器中甚至无意义。RAISE EXCEPTION 才会终止当前事务，等效于自动回滚。

如果 DELETE 是批量操作（如 WHERE id IN (1,2,3)），只要其中一行触发 RAISE EXCEPTION，整条语句就失败，其余行也删不掉。

PG 12+ 支持 WHEN 子句过滤触发时机，比在函数体里写 IF 更轻量，但只能用简单布尔表达式，不能查表
别在触发器里做 COUNT(*) 或 SUM() 类聚合，性能差、结果不准、还可能报错
需要强一致性校验时，可用 SELECT ... FOR SHARE 锁住关联行，避免并发干扰

TRUNCATE 和 DROP TABLE 根本不触发这些触发器

TRUNCATE TABLE 和 DROP TABLE 是 DDL 操作，所有主流数据库的行级触发器（BEFORE/AFTER/INSTEAD OF DELETE）都监听不到它们。MySQL 完全不支持拦截 TRUNCATE；PostgreSQL 可通过 EVENT TRIGGER 拦截 TRUNCATE 和 DROP，但需额外建模且版本要求高（PG 9.3+）。

真正起作用的只有权限控制：对普通账号执行 REVOKE DROP, TRUNCATE ON mydb.* FROM 'app_user'@'%'，并确认 SHOW GRANTS 输出里没有相关权限。