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