I have an SQL query like this:
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
and I have this trigger that does not let me do the UPDATE above:
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();
Note the comparison part should be ok, because
select 428872 = (0.00428872 * 100000000::BIGINT)::BIGINT returns true.
I guess the problem is with the chained UPDATEs not updating both tables at once.
Replacing BEFORE with AFTER helps, but I don't understand all implications...
I actually read about CONSTRAINT TRIGGER, DEFERRABLE, INITIALLY DEFERRED and REFERENCING and I still understand nothing, but most if not all of them use AFTER TRIGGER, not BEFORE, it's even hard to test all possible combinations.
How to update the trigger so the top-most SQL query passes?
