PostgreSQL中DELETE触发器触发条件始终不满足的问题排查

问题根源 #

在BEFORE DELETE触发器的函数中，错误使用了NEW变量。DELETE操作触发的触发器里，NEW是未定义的（因为没有新数据插入或更新），必须使用OLD来引用即将被删除的原记录数据。

修正后的触发器函数 #

CREATE OR REPLACE FUNCTION can_delete_policy_test()
  RETURNS TRIGGER AS $func$
BEGIN
  -- 通过OLD获取待删除记录的from和to字段
  IF OLD."from" = current_user::varchar OR 
     OLD."to" = current_user::varchar THEN
    RETURN OLD; -- DELETE触发器中返回OLD表示允许执行删除操作
  ELSE 
    RAISE EXCEPTION 'You have no permission to delete this record.';
  END IF;
END;
$func$ LANGUAGE plpgsql;

触发器重建（若已存在旧触发器） #

DROP TRIGGER IF EXISTS policy_test_delete_trigger ON policy_test;
CREATE TRIGGER policy_test_delete_trigger
BEFORE DELETE ON policy_test
FOR EACH ROW 
EXECUTE FUNCTION can_delete_policy_test();

逻辑说明 #

执行DELETE操作时，触发器会逐行校验待删除记录（OLD）的from或to字段是否匹配当前用户current_user，仅满足条件的记录会被允许删除，其余记录触发权限异常。

内容的提问来源于stack exchange，提问作者Zübeyir