Re: Trigger on Update

This works. Thanks for response.
----- Original Message -----
From: "mark reid" <pgsql(at)markreid(dot)org>
To: <sunithab(at)travelpost(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Sent: Friday, July 15, 2005 11:16 AM
Subject: Re: [GENERAL] 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(at)travelpost(dot)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;
>
>