Trigger on Update
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;
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/
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:
Show quoted text
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;
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
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
Show quoted text
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;