postgresqltemporal-database

insert values on trigger in temporal tables in postgres


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?

Solution

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