Postgres Pro
Downloads
Home   >   mailing lists

Thread: Trigger on Update

Trigger on Update

From
Date:
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;

Re: Trigger on Update

From
"Joshua D. Drake"
Date:
> Anybody know what is the syntax I have to use in update statement.
>

Try using now() instead of CURRENT_DATE.

>
>
> 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;


--
Your PostgreSQL solutions company - Command Prompt, Inc. 1.800.492.2240
PostgreSQL Replication, Consulting, Custom Programming, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/

Re: Trigger on Update

From
Mike Rylander
Date:
On 7/15/05, sunithab@travelpost.com <sunithab@travelpost.com> wrote:
[snip]
>
> 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 */

    NEW.updated_date = NOW();
    RETURN NEW;

> end;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

See http://www.postgresql.org/docs/8.0/interactive/plpgsql-trigger.html


--
Mike Rylander
mrylander@gmail.com
GPLS -- PINES Development
Database Developer
http://open-ils.org

Re: Trigger on Update

From
Date:
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;
>
>

Re: Trigger on Update

From
mark reid
Date:
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;