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

På tirsdag 29. juli 2014 kl. 12:01:48, skrev Pavel Stehule <pavel.stehule@gmail.com>:

 

 

2014-07-29 11:59 GMT+02:00 Andreas Joseph Krogh <andreas@visena.com>:

På tirsdag 29. juli 2014 kl. 11:56:17, skrev Pavel Stehule <pavel.stehule@gmail.com>:

Hi

 

2014-07-29 11:52 GMT+02:00 Andreas Joseph Krogh <andreas@visena.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?

 

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?

 

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com