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)
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.