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?
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.
> > 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 >