Thread: update trigger not working

I'm trying to set up a trigger that simply updates a
field's corresponding timestamp to now() whenever the
field is updated. But it's not working. Trying to
debug, I commented out the inner IF and END and the
log seemed to indicate infinite recursion occurred. My
next guess is that perhaps NULL's in OLD.stuff is
causing the IF to behave other than what I expect.

Thanks for any help!
CSN



CREATE or REPLACE function update_ts() returns trigger
as $end$

BEGIN

IF (TG_OP='UPDATE') THEN

    IF (OLD.stuff != NEW.stuff) THEN
        UPDATE table1
            set stuff_ts=now()
            where id=NEW.id;
    END IF;

END IF;

RETURN NULL;

END;

$end$ language plpgsql;

CREATE TRIGGER update_ts AFTER UPDATE ON table1 FOR
EACH ROW EXECUTE PROCEDURE update_ts();





__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

> I'm trying to set up a trigger that simply updates a
> field's corresponding timestamp to now() whenever the
> field is updated. But it's not working. Trying to
> debug, I commented out the inner IF and END and the
> log seemed to indicate infinite recursion occurred. My
> next guess is that perhaps NULL's in OLD.stuff is
> causing the IF to behave other than what I expect.

Let me see if I have this right.

You have an 'after-update' trigger on a table that does an update on that
same table, and you're wondering why that creates an infinite loop?

You need to do this in a 'before-update' trigger and set NEW.timestamp,
then return NEW.
--
Mike Nolan

On Wed, 2005-10-19 at 19:26 -0700, CSN wrote:
> I'm trying to set up a trigger that simply updates a
> field's corresponding timestamp to now() whenever the
> field is updated. But it's not working. Trying to
> debug, I commented out the inner IF and END and the
> log seemed to indicate infinite recursion occurred.

Yes.  On update, you are updating again...for ever.  What you need to do
is to modify the NEW record in a BEFORE trigger.

> CREATE or REPLACE function update_ts() returns trigger
> as $end$
>
> BEGIN
>
> IF (TG_OP='UPDATE') THEN
>
>     IF (OLD.stuff != NEW.stuff) THEN
>         UPDATE table1
>             set stuff_ts=now()
>             where id=NEW.id;

Instead of this, just do:

                  NEW.stuff_ts = now();

>     END IF;
>
> END IF;
>
> RETURN NULL;

RETURN NEW;

>
> END;
>
> $end$ language plpgsql;
>
> CREATE TRIGGER update_ts AFTER UPDATE ON table1 FOR

and make this a BEFORE trigger

> EACH ROW EXECUTE PROCEDURE update_ts();

--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html

CSN <cool_screen_name90001@yahoo.com> writes:

> I'm trying to set up a trigger that simply updates a
> field's corresponding timestamp to now() whenever the
> field is updated. But it's not working. Trying to
> debug, I commented out the inner IF and END and the
> log seemed to indicate infinite recursion occurred. My
> next guess is that perhaps NULL's in OLD.stuff is
> causing the IF to behave other than what I expect.

Make it a BEFORE UPDATE trigger and just set NEW.stuff_ts to now(),
rather than doing another query inside the trigger function.

-Doug