Thread: Trigger Question

Trigger Question

From

"Jason Lee"

Date:

14 March 2007, 12:28:11

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

Re: Trigger Question

From

Richard Huxton

Date:

14 March 2007, 12:37:45

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

Re: Trigger Question

From

Terry Lee Tucker

Date:

14 March 2007, 12:42:14

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

Re: Trigger Question

From

"Jason Lee"

Date:

14 March 2007, 12:43:31

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


> -----Original Message-----
> From: Richard Huxton [mailto:dev@archonet.com]
> Sent: Wednesday, March 14, 2007 10:38 AM
> To: Jason Lee
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Trigger Question
>
> 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
>

Re: Trigger Question

From

Alban Hertroys

Date:

14 March 2007, 12:43:40

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


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