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

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>