Can I start Update row in After Insert trigger function?
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
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
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):
Show quoted text
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.htmlBut 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_OPData 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
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