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 [EMAIL PROTECTED] www.turbocorp.com ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
