postgresql - PostgreSQL TRIGGER on WITH (UPDATE ..)UPDATE 在 2 个表上

Question

我有一个这样的 SQL 查询：

    WITH whatever1 AS (
        UPDATE
            table1
        SET
            field1 = <some number>
        WHERE
            id = <id>
        RETURNING id, field1
    )
    UPDATE
        table2
    SET
        field2 = (whatever1.field1 * 100000000::BIGINT)::BIGINT
    FROM
        whatever1
    WHERE
        field3 = whatever1.id
    RETURNING
        field3 AS whatever2

我有这个触发器不允许我执行UPDATE上述操作：

CREATE FUNCTION public.function1()
RETURNS TRIGGER LANGUAGE plpgsql AS
$function$
BEGIN

    IF NEW.field1 != OLD.field1 THEN

        IF (NEW.field1 * 100000000::BIGINT)::BIGINT != (SELECT "field2" FROM table2 WHERE NEW.id = field3) THEN
            RAISE EXCEPTION 'Help me with this trigger so this exception is not raised.';
        END IF;

    END IF;

    RETURN NEW;
END;
$function$;

CREATE TRIGGER trigger1 BEFORE
    INSERT OR UPDATE ON public.table1
    FOR EACH ROW EXECUTE PROCEDURE function1();

注意比较部分应该没问题，因为 select 428872 = (0.00428872 * 100000000::BIGINT)::BIGINT返回true.

我想问题在于链式UPDATEs 没有同时更新两个表。BEFORE用帮助替换AFTER，但我不明白所有的含义......

我实际上阅读了CONSTRAINT TRIGGER, DEFERRABLE,INITIALLY DEFERRED并且REFERENCING我仍然一无所知，但是大多数（如果不是全部）都使用AFTERTRIGGER，而不是BEFORE，甚至很难测试所有可能的组合。

如何更新触发器以使最顶层的 SQL 查询通过？

标签： postgresqlsql-updatewith-statementpostgresql-triggers