Postgresql Triggers: switch from after to before the trigger dont fire

Question

Hi guys, i have this simple plpgsql function:

CREATE FUNCTION "update_times" () RETURNS trigger AS '
    BEGIN
        NEW.update_time = NOW();
        RETURN NEW;
    END;'
LANGUAGE "plpgsql";

--The trigger:
CREATE TRIGGER test_update_time BEFORE UPDATE ON contact FOR EACH ROW EXECUTE PROCEDURE update_times();

and this works well, but only with BEFORE triggers...

I prefern to fire the trigger after the update, so i changed the function and the trigger itself as:

CREATE FUNCTION "update_times_after" () RETURNS trigger AS '
    BEGIN
        OLD.update_time = NOW();
        RETURN OLD;
    END;'
LANGUAGE "plpgsql";

--The trigger:
CREATE TRIGGER test_update_time_after AFTER UPDATE ON contact FOR EACH ROW EXECUTE PROCEDURE update_times_after();

But the AFTER trigger dont work (the trigger dont fire or the function fail).

What i am doing wrong?

Answer 1

After trigger is run when the data is already saved to table. So no modification (especially on OLD.!) doesn't make any sense.

If you want to change the data that is save to disk, you have to do it BEFORE it is saved.