Postgresql trigger at new row

Question

I created a trigger

CREATE TRIGGER trigger_name
AFTER INSERT OR UPDATE
ON schema.table
FOR EACH STATEMENT
EXECUTE PROCEDURE audit_trigger();

That seems to work. Then I have the function:

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER
LANGUAGE 'plpgsql'
AS $$
BEGIN

UPDATE schema.table SET NEW.changed_at = current_date, NEW.changed_by = current_user ;

END;
$$;

When I do an INSERT or an UPDATE at this table, it should automatically insert the time and name of user. The creation of the trigger and the function work properly (without error or warning). But when I do an INSERT at the table, I get this:

ERROR: FEHLER:  Spalte »new« von Relation »table« existiert nicht
LINE 1: UPDATE flug.flugplan SET NEW.changed_at = current_date, NEW....
                                 ^
QUERY:  UPDATE schema.tableSET NEW.changed_at = current_date, NEW.changed_by = current_user
CONTEXT:  PL/pgSQL-Funktion audit_trigger() Zeile 6 bei SQL-Anweisung

For the english speakers: It just says that the column >>new<< from the table doesnt exists.

Even if you got the syntax right you don't want to do this as you will set up a recursion as the `UPDATE` begets an `UPDATE` which begets an `UPDATE` and so on. See @Bjarni Ragnarsson answer. — Adrian Klaver, Jan 02 '21 at 18:34

score 3 · Accepted Answer · edited Jan 13 '21 at 20:39

To update fields in the inserted/updated row you need to do something like this:

NEW.changed_at := current_date;   
NEW.changed_by := current_user;
return NEW;

Your code will probably not work as expected as the trigger is defined as AFTER trigger and changes done to NEW are not stored. You need to change it to BEFORE. Audit triggers are indeed often after triggers as they are made to record changes done to table rows but in that case they are not used to alter the data in the table being audited.

Postgresql trigger at new row

1 Answers1