Prevent Infinite Recursion

Dev Shed Forums Sponsor:

January 8th, 2012, 10:37 PM

freshquiz

Registered User

Join Date: Jan 2012

Posts: 3

Time spent in forums: 36 m 29 sec
Reputation Power: 0

Prevent Infinite Recursion

Hi guys,

I have come across an extremely annoying problem in Postgresql.

I am trying to use this trigger:

Code:

CREATE FUNCTION next_calibration() RETURNS trigger AS $next_calibration$
    BEGIN
       IF TG_WHEN = 'AFTER' THEN

          IF TG_OP = 'INSERT' THEN

             UPDATE mi_calib_equip SET next_calib_date = last_calib_date + interval '6 months';

          ELSIF TG_OP = 'UPDATE' THEN

             UPDATE mi_calib_equip SET next_calib_date = last_calib_date + interval '6 months';
             
          END IF;
       END IF;
       RETURN NEW;
    END;
$next_calibration$ LANGUAGE plpgsql;

CREATE TRIGGER next_calibration AFTER INSERT OR UPDATE ON mi_calib_equip
EXECUTE PROCEDURE next_calibration();

I know the SQL in the trigger works fine because it runs perfectly on its own, but when I run it in this trigger, I get a stack error (pretty sure it is infinite recursion).
I'm pretty sure the problem is that I am using an UPDATE SQL statement in my trigger which is for some stupid reason, firing the function over and over.
Why is this so? I could understand if I was calling the function over and over, but this slight cross of UPDATE in the SQL statement and trigger is causing recursion. That is really stupid in my opinion.

Does anyone know of a way to overcome this problem?
I cannot use BEFORE UPDATE and NEW. because I need the update value (last_calib_date) from the user after it has been input.

Any help would be much appreciated. Thanks guys.

January 9th, 2012, 01:38 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,506

Time spent in forums: 1 Month 2 Weeks 5 Days 2 h 54 m 8 sec
Reputation Power: 283

Quote:

Why is this so?

Because if a trigger is defined every UPDATE must fire that trigger. Otherwise the trigger doesn't make sense.

Quote:

I cannot use BEFORE UPDATE and NEW.

That's the only way to solve this.

Quote:

because I need the update value (last_calib_date) from the user after it has been input.

What do you mean with "after it has been input"? Any data that is sent to the database is "after the user has put it in". Where is the last_calib_date calculated?

Last edited by shammat : January 9th, 2012 at 01:42 AM.

January 9th, 2012, 04:11 PM

freshquiz

Registered User

Join Date: Jan 2012

Posts: 3

Time spent in forums: 36 m 29 sec
Reputation Power: 0

Ok, well that is annoying.

Sorry I will try to make it clearer.

last_calib_date will be changed by the user. I want the trigger (or whatever else will do this) to fire AFTER the record has been saved, in order to collect that latest value of last_calib_date that the user just changed to. I don't want the previous one, I wan't the latest one, hence if I use BEFORE I can't get the new can I?

January 9th, 2012, 04:18 PM

freshquiz

Registered User

Join Date: Jan 2012

Posts: 3

Time spent in forums: 36 m 29 sec
Reputation Power: 0

Ahhh forgive me shammat, you were on the money.

I had tried BEFORE and NEW. before, but for some it had not worked, but now it works perfectly.

Thanks shammat !

So this is my working code:

Code:

CREATE FUNCTION next_calibration() RETURNS trigger AS $next_calibration$
    BEGIN
       IF TG_WHEN = 'BEFORE' THEN

          IF TG_OP = 'INSERT' THEN

             NEW.next_calib_date = NEW.last_calib_date + interval '6 months';

          ELSIF TG_OP = 'UPDATE' THEN

             NEW.next_calib_date = NEW.last_calib_date + interval '6 months';
             
          END IF;
       
       END IF;
       RETURN NEW;
    END;
$next_calibration$ LANGUAGE plpgsql;

CREATE TRIGGER next_calibration BEFORE INSERT OR UPDATE ON mi_calib_equip
FOR EACH ROW
EXECUTE PROCEDURE next_calibration();

January 9th, 2012, 04:22 PM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,506

Time spent in forums: 1 Month 2 Weeks 5 Days 2 h 54 m 8 sec
Reputation Power: 283

Quote:

Originally Posted by freshquiz

hence if I use BEFORE I can't get the new can I?

Yes you can get it. That's what the "new" and "old" records are supplied for in a trigger function. And both are available in a BEFORE trigger.

In your case the "user supplied" (new) value will be available in new.last_calib_date and the previous (old) value would be available in old.last_calib_date

All you need to do, is to use it in an assignment:

Code:

CREATE FUNCTION next_calibration() RETURNS trigger AS $next_calibration$
    BEGIN
       new.next_calib_date = new.last_calib_date + interval '6 months';
       RETURN NEW;
    END;
$next_calibration$ LANGUAGE plpgsql;

CREATE TRIGGER next_calibration BEFORE INSERT OR UPDATE ON mi_calib_equip
EXECUTE PROCEDURE next_calibration();

As the trigger is defined as "BEFORE" there is also no need to check the TG_WHEN. That would only be necessary if you use the same trigger function for a BEFORE and an AFTER trigger.