oracle-databaseplsqltriggersdatabase-triggerscd

Implement type II SCD in Oracle trigger with using merge


For the life of me I can't’ get the following query to work…essentially it’s a modification of the following ticket: Trigger with Merge statement in oracle.

CREATE OR REPLACE TRIGGER TABLE_UPDATE
  AFTER INSERT OR UPDATE ON DIM_TABLE
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF INSERTING OR UPDATING
THEN
    MERGE INTO DIM_TABLE T_1
          USING( SELECT COL_1, max(VALID_FROM) AS LAST_DATE FROM 
DIM_TABLE
          GROUP BY COL_1) T_2
          ON (T_1. COL_1= T_2. COL_1)

    WHEN NOT MATCHED THEN INSERT (T_1. VALID_FROM) VALUES(SYSDATE)

    WHEN MATCHED THEN
      UPDATE
      SET T_1.VALID_UNTIL = T_2.LAST_DATE
      WHERE T_1. VALID_UNTIL is null 
      AND T_1. VALID_FROM <> T_2.LAST_DATE;
   COMMIT;
 END IF; 
END;

Desired functionality: a value is entered into the database (COL_1). If the column does not have a match then the VALID_FROM date is entered as sysdate. If there is a match then the script should update the VALID_UNTIL value for the previous row (which was null until now because it was still valid).

I keep getting the following errors: deadlock and maximum level of recursions exceeded (50)


Solution

  • thank you @hol, @Alex Poole, I think I cracked it. I'm using a trigger for inserting data into the VALID_FROM every time anything gets added:

     CREATE TABLE "DIM_TEST" (  "COL_1" VARCHAR2(20 BYTE), "VALID_UNTIL" TIMESTAMP (6), 
    "DIM_TEST_PK" NUMBER, "VALID_FROM" TIMESTAMP (6) DEFAULT NULL) 
    
      create or replace TRIGGER T_DIM_TEST_DATE_INSERT
      BEFORE INSERT ON DIM_TEST
      FOR EACH ROW
      BEGIN
        :new.VALID_FROM := sysdate;
      END;
    

    and for the part which updates the previous row VALID_FROM when a duplicate is added I have the following trigger:

     create or replace TRIGGER TABLE_UPDATE
     after INSERT or update
     ON DIM_TEST
     FOR EACH ROW
     DECLARE
     PRAGMA AUTONOMOUS_TRANSACTION;
     BEGIN
     IF INSERTING
     THEN
        MERGE INTO DIM_TEST T_1
        USING (select :new.COL_1 from DUAL) T_2
          ON (T_1.COL_1 = :new.COL_1)
    
    WHEN MATCHED THEN
      UPDATE
      SET T_1.VALID_UNTIL = (SYSDATE)
      WHERE T_1. VALID_UNTIL is null;
      --AND T_1. VALID_FROM <> (SELECT max(VALID_FROM) FROM DIM_TEST);
     END IF;  
    COMMIT;
    END;
    

    The only downside is that if an existing row is updated to be a duplicate then the second trigger will not fire because of the IF INSERTING clause...when I change it to IF INSERTING or UPDATING then it will generate an infinite loop...but I can restrict users only to inserts so it shouldn't be a problem.