Postgres Pro
Downloads
Home   >   mailing lists

Thread: Can I start Update row in After Insert trigger function?

Can I start Update row in After Insert trigger function?

From
Durumdara
Date:
Hello!

I need to log some row changes in a table with JSON format (row_to_json).

In the Before Update I can select the row into a column with JSON format.
And in After Update I can log this column into an archive table.
Plus I have the prior data in the JSON_TEXT field.

It's ok.

FUNCTION Before_Update
...
BEGIN
    select row_to_json(thistable) into json_text_var 
       from thistable where id = OLD.id;
    NEW.json_text = json_text_var;
    RETURN NEW;
END;

FUNCTION After_Update
...
BEGIN
    insert into logtable select 'thistable', NEW.id, NEW.json_TEXT;
    RETURN NEW;
END;



But this technique isn't working in insert, because no OLD.id. and OLD row:

select row_to_json(thistable) into json_text_var 
from thistable where id = ???.id;  
No row!!! Only NEW.nn variables.

Only way if I call an update:

FUNCTION After_Insert
...
BEGIN
    -- We try to update the log with a dummy update
    -- This calls Before/After Update, logs + fills the json field
    update thistable set json_text = json_text where id = NEW.id;
    RETURN NEW;
END;

Does this cause problems?
If I call an Update in the Row before After_Insert finishes?

Version: PGSQL 9.6-11

Thank you for your help!

Best regards,
   dd

Re: Can I start Update row in After Insert trigger function?

From
Adrian Klaver
Date:
On 5/23/22 10:35 AM, Durumdara wrote:
> Hello!
> 
> I need to log some row changes in a table with JSON format (row_to_json).
> 
> In the Before Update I can select the row into a column with JSON format.
> And in After Update I can log this column into an archive table.
> Plus I have the prior data in the JSON_TEXT field.

You need to read:
https://www.postgresql.org/docs/current/plpgsql-trigger.html

> 
> But this technique isn't working in insert, because no OLD.id. and OLD row:
> 
>     select row_to_json(thistable) into json_text_var
>     from thistable where id = ???.id;
>     No row!!! Only NEW.nn variables.

Yes an INSERT is a new value only there is no old row(value) available. 
In the doc link I posted:

"
TG_OP

     Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE 
telling for which operation the trigger was fired.
"

Use this to modify what value(NEW/OLD) you are working with. Call 
trigger with UPDATE and INSERT.

> 
> Version: PGSQL 9.6-11
> 
> Thank you for your help!
> 
> Best regards,
>     dd
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Can I start Update row in After Insert trigger function?

From
Durumdara
Date:
Dear Adrian!

Thank you for the information. For me the main question is that:
Can I execute an UPDATE in the  AFTER INSERT trigger procedure?
Or is this confuses PGSQL, because prior operation (INSERT) isn't fully finished?

Thank you!

BR,
dd

Adrian Klaver <adrian.klaver@aklaver.com> ezt írta (időpont: 2022. máj. 23., H, 19:42):
On 5/23/22 10:35 AM, Durumdara wrote:
> Hello!
>
> I need to log some row changes in a table with JSON format (row_to_json).
>
> In the Before Update I can select the row into a column with JSON format.
> And in After Update I can log this column into an archive table.
> Plus I have the prior data in the JSON_TEXT field.

You need to read:
https://www.postgresql.org/docs/current/plpgsql-trigger.html

>
> But this technique isn't working in insert, because no OLD.id. and OLD row:
>
>     select row_to_json(thistable) into json_text_var
>     from thistable where id = ???.id;
>     No row!!! Only NEW.nn variables.

Yes an INSERT is a new value only there is no old row(value) available.
In the doc link I posted:

"
TG_OP

     Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE
telling for which operation the trigger was fired.
"

Use this to modify what value(NEW/OLD) you are working with. Call
trigger with UPDATE and INSERT.

>
> Version: PGSQL 9.6-11
>
> Thank you for your help!
>
> Best regards,
>     dd
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Can I start Update row in After Insert trigger function?

From
Adrian Klaver
Date:
On 5/23/22 23:17, Durumdara wrote:
> Dear Adrian!
> 
> Thank you for the information. For me the main question is that:
> Can I execute an UPDATE in the  AFTER INSERT trigger procedure?
> Or is this confuses PGSQL, because prior operation (INSERT) isn't fully 
> finished?

It is spelled out here:

https://www.postgresql.org/docs/current/sql-createtrigger.html

"The trigger can be specified to fire before the operation is attempted 
on a row (before constraints are checked and the INSERT, UPDATE, or 
DELETE is attempted); or after the operation has completed (after 
constraints are checked and the INSERT, UPDATE, or DELETE has 
completed); or instead of the operation (in the case of inserts, updates 
or deletes on a view)."

So the INSERT has completed in an AFTER trigger.

> 
> Thank you!
> 
> BR,
> dd
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com