PL/SQL: Avoiding recursive triggers when updating columns

Question

I spent hours to find an adequate solution for this problem, so I create this question in Q&A style after I found a solution.

I've got a table like this:

CREATE TABLE SoftwareVersion
(
  ID NUMBER NOT NULL,
  DeviceID NUMBER NOT NULL,
  ReadoutDate DATE NOT NULL,
  Version VARCHAR2(20 CHAR) NOT NULL,
  NextReadoutDate DATE NULL
);

This table contains software version codes of a device. Each device can have one or more software versions. The import process, which executes the INSERT statements for this table, only fills ID, DeviceID, ReadoutDate and Version. The ReadoutDate is the current timestamp of the import process. So the first occurence of a software version is assumed as "the software version is valid since the readout date".

My problem was now that I need a range for the software version. From which timestamp to which timestamp was each software version valid?

To improve performance (and since I already got triggers for that table) I added the NextReadoutDate column which shall be maintained by the triggers. It will receive the next valid ReadoutDate value for that DeviceID. So each software version will become a range (valid from ... to).

To avoid the mutating table problem (ORA-04091) I collect all updated information of the statement I am using an AFTER ROW trigger like this:

CREATE OR REPLACE TRIGGER TRG_SoftwareVersion1
  AFTER INSERT OR UPDATE OR DELETE ON SoftwareVersion
  FOR EACH ROW
BEGIN
  IF UPDATING THEN
    IF :OLD.DeviceID = :NEW.DeviceID AND :OLD.ReadoutDate = :NEW.ReadoutDate OR
       (:OLD.DeviceID IS NULL OR :OLD.ReadoutDate IS NULL) AND
       (:NEW.DeviceID IS NULL OR :NEW.ReadoutDate IS NULL) THEN
      -- Nothing to do
      RETURN;
    END IF;
  END IF;

  -- Evaluate later
  INSERT INTO
    SoftwareVersion_TrgHelper
    (
      OldDeviceID,
      OldReadoutDate,
      NewDeviceID,
      NewReadoutDate
    )
  VALUES
    (
      :OLD.DeviceID,
      :OLD.ReadoutDate,
      :NEW.DeviceID,
      :NEW.ReadoutDate
    );
END;

After that I update the table within an AFTER STATEMENT trigger like this:

CREATE OR REPLACE TRIGGER TRG_SoftwareVersion2
  AFTER INSERT OR UPDATE OR DELETE ON SoftwareVersion
DECLARE
  CURSOR cCursorMain IS SELECT * FROM SoftwareVersion_TrgHelper FOR UPDATE;
  vOldDeviceID               NUMBER;
  vOldReadoutDate            DATE;
  vNewDeviceID               NUMBER;
  vNewReadoutDate            DATE;
BEGIN
  OPEN cCursorMain;
  LOOP
    FETCH cCursorMain INTO vOldDeviceID, vOldReadoutDate, vNewDeviceID, vNewReadoutDate;
    EXIT WHEN cCursorMain%NOTFOUND;

    IF UPDATING OR DELETING THEN
      UPDATE
        SoftwareVersion SV
      SET
        SV.NextReadoutDate = (SELECT MIN(SV2.ReadoutDate) KEEP (DENSE_RANK FIRST ORDER BY SV2.ReadoutDate ASC, SV2.ID ASC) FROM SoftwareVersion SV2 WHERE SV.DeviceID = SV2.DeviceID AND SV.ReadoutDate < SV2.ReadoutDate)
      WHERE
        SV.DeviceID      = vOldDeviceID AND
        SV.ReadoutDate  <= vOldReadoutDate;
    END IF;
    IF UPDATING OR INSERTING THEN
      UPDATE
        SoftwareVersion SV
      SET
        SV.NextReadoutDate = (SELECT MIN(SV2.ReadoutDate) KEEP (DENSE_RANK FIRST ORDER BY SV2.ReadoutDate ASC, SV2.ID ASC) FROM SoftwareVersion SV2 WHERE SV.DeviceID = SV2.DeviceID AND SV.ReadoutDate < SV2.ReadoutDate)
      WHERE
        SV.DeviceID      = vNewDeviceID AND
        SV.ReadoutDate  <= vNewReadoutDate;
    END IF;
    DELETE FROM SoftwareVersion_TrgHelper WHERE CURRENT OF cCursorMain;
  END LOOP;
  CLOSE cCursorMain;
END;
/

Unfortunately I got an ORA-00036 as soon as I was executing the import process again. I needed a way to avoid the trigger recursion.

Answer 1

Oracle doesn't provide functions like TRIGGER_NESTLEVEL (as SQL server does) and it seems that there are not many functions to avoid recursion at all (you could use session variables or something like this, but this can cause other errors).

My simple solution is to let my triggers only trigger when specific columns get modified. This is possible, because my UPDATE statement within the trigger only modifies the NextReadoutDate column.

Instead of:

AFTER INSERT OR UPDATE OR DELETE ON SoftwareVersion

I use:

AFTER INSERT OR UPDATE OR DELETE OF DeviceID, ReadoutDate ON SoftwareVersion

And everything is fine then.