Re: Trigger on Update

This works. Thanks for response.
----- Original Message -----
From: "mark reid" <pgsql@markreid.org>
To: <sunithab@travelpost.com>
Cc: <pgsql-general@postgresql.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@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;
>
>