PostgreSQL创建带主键条件的行删除前置触发器问题

给定表结构：

CREATE TABLE public."table" (
    c1 int4 NOT NULL,
    c2 int4 NOT NULL,
    c3 int4 NOT NULL,
    d varchar(20) NULL,
    e varchar(20) NULL,
    f date NULL
        
    CONSTRAINT table_pkey PRIMARY KEY (c1, c2, c3)
);

需求：仅当DELETE语句的WHERE子句同时指定c1、c2、c3三个主键字段的等值条件时，允许删除；若仅指定其中1个或2个字段（或任意组合），则禁止删除并抛出错误。例如：

• 错误示例：delete from public."table" where c1=X（仅指定c1）
• 正确示例：delete from public."table" where c1=X and c2=Y and c3=Z（同时指定三个主键）

尝试了行级触发器但未达到预期：

CREATE OR REPLACE FUNCTION verify_delete()
RETURNS TRIGGER AS $$
BEGIN

    IF OLD.c1 IS NULL OR OLD.c2 IS NULL OR OLD.c3 IS NULL THEN
        RAISE EXCEPTION 'ERROR!';
    END IF;

    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_verify_delete
BEFORE DELETE ON "table"
FOR EACH ROW
EXECUTE procedure verify_delete();

为什么原触发器无效 #

原触发器是行级触发器（FOR EACH ROW），它针对每一条即将被删除的记录做检查。但由于c1、c2、c3是主键且定义为NOT NULL，OLD变量中的这三个字段永远不会为NULL，导致IF条件永远不触发，完全无法限制不符合要求的DELETE语句。

正确实现方案 #

需要使用语句级触发器（FOR EACH STATEMENT），直接检查整个DELETE语句的WHERE子句是否符合要求。具体实现如下：

1. 创建触发器函数 #

CREATE OR REPLACE FUNCTION verify_delete_statement()
RETURNS TRIGGER AS $$
DECLARE
    query_text text;
    has_c1 boolean := false;
    has_c2 boolean := false;
    has_c3 boolean := false;
BEGIN
    -- 避免递归调用（防止触发器内的操作再次触发自身）
    IF pg_trigger_depth() > 1 THEN
        RETURN NULL;
    END IF;

    -- 获取当前执行的SQL语句
    query_text := current_query();
    
    -- 检查是否包含c1的等值条件（兼容大小写、表别名、字段引号）
    IF query_text ~* '\m(c1|public\."table"\.c1|".*"\.c1)\M\s*=' THEN
        has_c1 := true;
    END IF;
    
    -- 检查c2的等值条件
    IF query_text ~* '\m(c2|public\."table"\.c2|".*"\.c2)\M\s*=' THEN
        has_c2 := true;
    END IF;
    
    -- 检查c3的等值条件
    IF query_text ~* '\m(c3|public\."table"\.c3|".*"\.c3)\M\s*=' THEN
        has_c3 := true;
    END IF;
    
    -- 若三个主键的等值条件不全，抛出异常
    IF NOT (has_c1 AND has_c2 AND has_c3) THEN
        RAISE EXCEPTION '删除操作必须同时指定c1、c2、c3三个主键字段的等值条件';
    END IF;
    
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;

2. 创建语句级触发器 #

CREATE TRIGGER trigger_verify_delete_statement
BEFORE DELETE ON public."table"
FOR EACH STATEMENT
EXECUTE FUNCTION verify_delete_statement();

说明 #

• 该方案通过正则表达式匹配当前SQL语句，检查是否同时包含三个主键的等值条件，兼容字段大小写、表别名、带引号的字段名等场景。
• 如果需要更严格的校验（比如限制只能有三个主键条件、不允许其他筛选条件），可以修改正则表达式或补充逻辑。
• 对于参数化查询（如PREPARE语句），current_query()会显示占位符（如$1），此时正则匹配会失效，这种场景需要通过解析查询语法树来实现更精准的检查（可借助pg_parse_query函数结合语法树遍历）。

内容的提问来源于stack exchange，提问作者Manuel Córdoba