CODEWITHSUNDEEP
postgresqltriggersplpgsql
KerryKilian
KerryKilian

Reputation: 95

Postgresql trigger at new row

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.

Upvotes: 1

Views: 2317

Answers (1)

Bjarni Ragnarsson
Bjarni Ragnarsson

Reputation: 1781

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.

Upvotes: 3

Related Questions