Trigger using adjusted OLD / NEW values

Question

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 'something done here';
  elsif (tg_op = 'UPDATE') then 
    update NEW set edited_date = now(), edited_by = current_user;
    insert into my_table_hist select NEW.*;
  elsif (tg_op = 'DELETE') then 
    return 'something done 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 it clearly doesn't work like that, so how should I rewrite this trigger function to make it work?

Laurenz Albe Laurenz Albe · Accepted Answer · 2020-09-29T05:28:27

There is much amiss here:

trigger functions must return a row, typically (a modified) NEW, or NULL to abort the operation
you do not update NEW since it is not a table, but you assign to its columns, like
```
NEW.created := current_timestamp;
```
for BEFORE DELETE triggers NEW is NULL, since there is no new version of the row

Trigger using adjusted OLD / NEW values

1 Answers