Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: 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.

  2. #2
    Join Date
    Jan 2012
    Posts
    2
    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();

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •