Update field updated by trigger

Question

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!

score 4 · Accepted Answer · edited May 23 '17 at 12:12

4

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.

edited May 23 '17 at 12:12

Community

1
1

answered Apr 21 '13 at 02:03

Craig Ringer

307,061
76
688
778

Because I am not familiar with GUC and have a lot of fields in this table (for solution D), I will disable trigger I think. Thank you! – HoGo Apr 23 '13 at 09:39
http://koo.fi/blog/2013/01/08/disable-postgresql-triggers-temporarily/ I think I would rather disable all triggers in current session than just one, but globally. – HoGo Apr 23 '13 at 09:42
Oh, you can use the normal `set_config` SQL function instead of the `SET` statement too. – Craig Ringer May 20 '13 at 08:06

Update field updated by trigger

1 Answers1