On Fri, 12 Apr 2002, Ed Loehr wrote:
> This looks like a bug. Only question is whose. The triggered update of a
> timestamp field is not working. I can see the expected outputs in the log,
> but the timestamp update is not sticking. Can someone identify my error or
> test this on 7.2.1 and tell me if the bug is mine (most likely) or a bug in
> PL/pgSQL?
>
> -- Simple function to update the 'updated' column to the current time
> CREATE FUNCTION set_update_time() RETURNS OPAQUE AS
> 'BEGIN
> NEW.updated := ''now'';
> RAISE NOTICE ''Resetting %.updated to %'', TG_RELNAME, NEW.updated;
> RETURN NEW;
> END;'
> LANGUAGE 'plpgsql';
>
> CREATE TABLE plpgsql_test (
> id SERIAL,
> created TIMESTAMP NOT NULL DEFAULT now(),
> updated TIMESTAMP NOT NULL DEFAULT now()
> );
>
> CREATE TRIGGER plpgsql_test_trigger
> AFTER UPDATE ON plpgsql_test FOR EACH ROW
> EXECUTE PROCEDURE set_update_time();
I think you want a before update trigger since you're
modifying NEW.