So I am new to using procedures and triggers and it is really confusing me
I have used temporal tables
and want to basically create a history table of records inserted,updated or deleted.
Infact I have created my history table and works fine when I use this trigger sql
DROP TRIGGER if exists versioning_trigger on mytable;
CREATE TRIGGER versioning_trigger BEFORE INSERT OR UPDATE OR DELETE ON mytable FOR EACH ROW EXECUTE PROCEDURE versioning('sys_period', 'table_history', true);
This creates records of the rows updated or deleted,precisely copies the old row record from mytable
into table_history
table and updates the record in mytable
.But I want to insert the updated record from mytable
to table_history
also so that it has records of all types('current active record'and 'record before updation').Also insert some other fields in table_history
when the trigger is executed.
I want to ask
CREATE TRIGGER
query in temporal_tables?table_history
on trigger execution? How can I accomplish this?https://www.postgresql.org/docs/current/static/plpgsql-trigger.html
A trigger procedure is created with the CREATE FUNCTION command, declaring it as a function with no arguments and a return type of trigger
and also
https://www.postgresql.org/docs/current/static/sql-createtrigger.html
Determines whether the function is called before, after, or instead of the event.
NEW
instead of OLD
for new valueshttps://www.postgresql.org/docs/current/static/plpgsql-trigger.html
NEW
Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is unassigned in statement-level triggers and for DELETE operations.