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
- How is it possible to have different trigger events(BEFORE or AFTER) together in one
CREATE TRIGGERquery in temporal_tables? - Is it possible to insert new field values in
table_historyon trigger execution? How can I accomplish this?
https://www.postgresql.org/docs/current/static/plpgsql-trigger.html
and also
https://www.postgresql.org/docs/current/static/sql-createtrigger.html
NEWinstead ofOLDfor new valueshttps://www.postgresql.org/docs/current/static/plpgsql-trigger.html