Trigger Question

On Wednesday 14 March 2007 11:15, Jason Lee wrote:

I'm trying to write a trigger that updates a date_changed field on a
record anytime that record is updated. I have a function written, and
the trigger created, but everytime I update the record, I get a
recursion limit error. It appears that the action performed by my
trigger is causing the trigger to fire. How do I avoid that. For the
record, here's my function (modeled after a trigger that works on SQL
Server. I'm pretty much a noob with plpgsql :)

declare begin
update unit_specification set date_changed = now() from
unit_specification us where us.id = NEW.id;
RETURN NEW;
end;

With the trigger created with

CREATE TRIGGER update_changed_ts AFTER INSERT OR UPDATE
ON unit_specification FOR EACH ROW
EXECUTE PROCEDURE public.update_unit_spec_changed_date();

Thanks for the help. :)

-----
Jason Lee, SCJP
Senior Software Engineer
http://www.iec-okc.com <http://www.iec-okc.com/&gt;

Your trigger needs to be a BEFORE UPDATE trigger and you simply set the value
of the field in the trigger as in: date_changed = current_date;
--
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812 Fax: (336) 372-6812 Cell: (336) 404-6987
terry@turbocorp.com
www.turbocorp.com

Jason Lee wrote:

I'm trying to write a trigger that updates a date_changed field on a
record anytime that record is updated. I have a function written, and
the trigger created, but everytime I update the record, I get a
recursion limit error.

[snip]

declare begin
update unit_specification set date_changed = now() from
unit_specification us where us.id = NEW.id;
RETURN NEW;
end;

Here, just do
NEW.date_changed = now();
RETURN NEW;

With the trigger created with

CREATE TRIGGER update_changed_ts AFTER INSERT OR UPDATE
ON unit_specification FOR EACH ROW
EXECUTE PROCEDURE public.update_unit_spec_changed_date();

Make this BEFORE insert or update.

--
Richard Huxton
Archonet Ltd

Thanks! That did the trick. I had tried that logic in the function,
but I had always used an AFTER trigger, so it didn't work. Thanks,
again. You've saved my sanity. :)

-----
Jason Lee, SCJP
Senior Software Engineer
http://www.iec-okc.com

Jason Lee wrote:

I'm trying to write a trigger that updates a date_changed field on a
record anytime that record is updated. I have a function written, and
the trigger created, but everytime I update the record, I get a
recursion limit error. It appears that the action performed by my

You probably want to use a BEFORE UPDATE trigger and assign now() to
NEW.date_changed.

trigger is causing the trigger to fire. How do I avoid that. For the
record, here's my function (modeled after a trigger that works on SQL
Server. I'm pretty much a noob with plpgsql :)

declare begin
update unit_specification set date_changed = now() from
unit_specification us where us.id = NEW.id;
RETURN NEW;
end;

With the trigger created with

CREATE TRIGGER update_changed_ts AFTER INSERT OR UPDATE
ON unit_specification FOR EACH ROW
EXECUTE PROCEDURE public.update_unit_spec_changed_date();

Thanks for the help. :)

-----
Jason Lee, SCJP
Senior Software Engineer
http://www.iec-okc.com <http://www.iec-okc.com/&gt;

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede

// Integrate Your World //