postgresql – Trigger using adjusted OLD / NEW values


I am trying to put together trigger which does multiple things:

  • before any insert updates automatically columns created_date and created_by for the inserted row and also inserts this row with updated values into archive (history) table

  • before any update updates automatically values edited_date and edited_by for the updated row and inserts this updated row (including updated values edited_date and edited_by) into archive table

  • before any delete…

How to write efficiently the before (or after) update trigger using adjusted NEW (or OLD) values? Here is my trigger:

create trigger my_trigger
before insert or update or delete on my_table
for each row execute procedure my_function();

And function where I would like to use updated new row:

create function my_function() returns trigger as $$ 
begin
  if (tg_op = 'INSERT') then return 'do something here';
  elsif (tg_op = 'UPDATE') then 
    update NEW set edited_date = now(), edited_by = current_user;
    insert into my_table_hist select NEW.*;
  if (tg_op = 'DELETE') then return 'do something here';
  end if;
  return null;
end;
$$ language plpgsql;

What I mean by this is before update I update NEW row columns to current time and current user, then the table gets updated to this new row with updated values, and finally this row with updated values is inserted also into archive (history) table. But I don’t think it works like that, does it?