Re: Update columns in the same table in a deferred constraint trigger

2014-07-29 12:05 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:

> På tirsdag 29. juli 2014 kl. 12:01:48, skrev Pavel Stehule <
> pavel(dot)stehule(at)gmail(dot)com>:
>
>
>
> 2014-07-29 11:59 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:
>>
>> På tirsdag 29. juli 2014 kl. 11:56:17, skrev Pavel Stehule <
>> pavel(dot)stehule(at)gmail(dot)com>:
>>
>> Hi
>>
>> 2014-07-29 11:52 GMT+02:00 Andreas Joseph Krogh <andreas(at)visena(dot)com>:
>>>
>>> Hi all.
>>>
>>> I have this simple schema:
>>>
>>> create table fisk(
>>> name varchar primary key,
>>> autofisk varchar
>>> );
>>>
>>> I want to update the column "autofisk" on commit based the value of
>>> "name", so I created this trigger:
>>>
>>> CREATE OR REPLACE FUNCTION fisk_tf() returns TRIGGER AS $$
>>> BEGIN
>>> raise notice 'name %', NEW.name;
>>> NEW.autofisk = NEW.name || CURRENT_TIMESTAMP::text;
>>> RETURN NEW;
>>> END;
>>> $$ LANGUAGE plpgsql;
>>>
>>> CREATE CONSTRAINT TRIGGER fisk_t AFTER INSERT OR UPDATE ON fisk
>>> DEFERRABLE INITIALLY DEFERRED
>>>
>>
>> It should be BEFORE INSERT OR UPDATE trigger
>>
>>
>> He he, yes - I know that will work, but I need the trigger to be run as
>> a constraint-trigger, on commit, after all the data is populated in other
>> tables and this table.
>>
>
> It is not possible - Postgres can change data only before writing
>
>
> Is there a work-around, so I in the trigger can issue for example:
>
> update fisk set autofisk = NEW.name || CURRENT_TIMESTAMP::text where name
> = NEW.name;
>
> without it also triggering the trigger?
>

theoretically yes - you can disable triggers via ALTER TABLE DISABLE TRIGGER

but then the code will be unmaintainable. Anything else is better than
dependency in triggers. You should to think about different solution.

Sometimes triggers can be replaced by functions directly called from
applications instead DML statements.

>
> I have tried this but the commit never returns, I think because it
> recursively triggers the trigger again for that modification.
>
> Will temporarily disabeling the trigger inside the trigger (in a
> transaction) work?
>

I really afraid of this strategy

Regards

Pavel

>
> --
> *Andreas Joseph Krogh*
> CTO / Partner - Visena AS
> Mobile: +47 909 56 963
> andreas(at)visena(dot)com
> www.visena.com
> <https://www.visena.com>
>
>