Prevent Infinite Recursion

freshquiz · #1 (**permalink**) 01-09-12, 00:03

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.

freshquiz · #2 (**permalink**) 01-09-12, 17:19

Never mind guys, I solve it and here is my solution:

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();

shammat · #3 (**permalink**) 01-09-12, 17:38

As your trigger is defined as BEFORE, there is no need to check TG_WHEN.

As you are applying the same code for INSERT and UPDATE there is also no need to check for TG_OP as well.

So you could get rid of all IF's and simply put the assignment into the body of the function

Internet Services

Web Development

Digital Marketing

Consumer Tech