Check constraint仅在Insert而不是updates上触发

Question

我的Check约束如下：

ALTER TABLE tablename
ADD CONSTRAINT check_duplicate_rows 
CHECK (reject_duplicate_rows(columnB, columnC, columnD) < 2);

我希望仅当您插入记录时才计算约束。

目前它对insert和update语句都是这样做的，问题是我的系统需要更新插入的行，而check约束阻止了更新。

reject_duplicate_rows函数如下：

CREATE OR REPLACE FUNCTION reject_duplicate_rows(columnB integer, columnC integer, columnD integer)
  RETURNS integer AS
$BODY$

DECLARE
    results INTEGER := 1;
    v_count INTEGER := 0;

BEGIN
    IF columnC <> 23 THEN
       RETURN results;
    END IF;

    SELECT total INTO v_count FROM 
        (SELECT  columnB,
                 columnC,
                 columnD,
                 count(*) AS  total 
        FROM     table_name
        WHERE B = columnB AND C = columnC AND D = columnD
        GROUP BY 1, 2, 3)
        as temp_table;

    IF COALESCE(v_count, 0) = 0 THEN
        RETURN results;
    END IF; 

    IF v_count >= 1 THEN
        results := 2;
    END IF;
    RETURN results;
EXCEPTION
  WHEN OTHERS THEN
    RETURN results;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 1000;
ALTER FUNCTION reject_duplicate_rows(integer, integer, integer)
  OWNER TO postgres

Answer 1

您是否尝试过创建UPDATE触发器？请参阅Creating postgresql trigger