Re: Trigger on Update

Hi,

Change it to a "BEFORE UPDATE" trigger, and set NEW.updated_date := now();

Otherwise each update produces another update produces another update....

-Mark.

sunithab@travelpost.com wrote:

> Can anybody help me creating a trigger on update trigger with update
> statement as below.
>
> This trigger fires after update on a table called note  to update the
> updated_date field.
>
> But when the table is updated the trigger is firing recursively.
>
> Anybody know what is the syntax I have to use in update statement.
>
>
>
> CREATE TRIGGER "trg_update_note_updated_date" AFTER UPDATE
> ON "property"."note" FOR EACH ROW
> EXECUTE PROCEDURE "property"."update_note_updated_date_trg"();
>
>
> CREATE OR REPLACE FUNCTION "property"."update_note_updated_date_trg"
> () RETURNS trigger AS
> $body$
> begin
>   /* New function body */
>        UPDATE property.note SET updated_date=CURRENT_DATE
>        WHERE note_id = NEW. note_id;
>     return null;
> end;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;