insert values on trigger in temporal tables in postgres

780 Views Asked by At

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

  1. How is it possible to have different trigger events(BEFORE or AFTER) together in one CREATE TRIGGER query in temporal_tables?
  2. Is it possible to insert new field values in table_history on trigger execution? How can I accomplish this?
1

There are 1 best solutions below

0
Vao Tsun On BEST ANSWER

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

  1. same trigger can't fire both before and after event - just create two triggers if you really need it

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

Determines whether the function is called before, after, or instead of the event.

  1. use NEW instead of OLD for new values

https://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.