CODEWITHSUNDEEP
postgresqltriggers
HoGo
HoGo

Reputation: 158

Update field updated by trigger

I have a table with multiple fields and an additional outofsync field. Created a trigger and trigger function to set outofsync field value to true before any update/insert.

Trigger:

CREATE TRIGGER goods_update_outofsync
  BEFORE UPDATE
  ON goods
  FOR EACH ROW
  EXECUTE PROCEDURE tg_update_goods_outofsync();

Trigger function:

CREATE OR REPLACE FUNCTION tg_update_goods_outofsync()
  RETURNS trigger AS
$BODY$
    BEGIN
    NEW.outofsync=true;
    RETURN NEW;
    END;
    $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION tg_update_goods_outofsync()
  OWNER TO postgres;

And now comes to a "simple" question I am not able to find answer: how to update manually outofsync field to false, because after each attempt it is automatically changed to true by trigger.

EDIT:

This almost works:

IF (NEW.outofsync = OLD.outofsync) THEN
    NEW.outofsync=true;
END IF;

Except when value of outofsync field is already false and I want to set it to false, because it became true then...

Thank you for your help in advance!

Upvotes: 2

Views: 1510

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324305

At least four options:

  • Set to in sync as another user and test current_user in the trigger;

  • Define a custom config variable (GUC) and SET LOCAL or set_config(...) it in the transaction before updating the in sync field; test that GUC in the trigger and change behaviour based on it;

  • Temporarily disable the trigger in the transaction before setting in sync;

  • Have the trigger check if all the other values are unchanged by the update and allow in sync to be set to true if no other values have changed. Use IS DISTINCT FROM for this test to handle nulls conveniently.

I'd probably use a custom GUC myself, with current_setting('my.guc') to fetch the value from within the trigger.

If you're on Pg 9.1 or older you must add my (or whatever you really call the prefix) to custom_variable_classes. In 9.2 and above any variable with a period (.) in it is assumed to be a custom variable.

See also passing user ID to triggers.

Upvotes: 4

Related Questions