In part one we say about Mutating Trigger and how PRAGMA AUTONOMOUS_TRANSACTION helped to resolve the same. Remember PRAGMA AUTONOMOUS_TRANSACTION is just an work around to perform.
Though Oracle allows us to create more than one trigger on the same table for the same timing, it has never guaranteed its order execution. Compound trigger in Oracle 11g allows us to define the order through the FOLLOWS clause. Yet another feature is it allows us to select on the same table for which the trigger has been fired. Thus this helps in mitigating the Mutating trigger issue.
Let us reqrite the same requirement as in Part1 with Oracle Compound Trigger.
Table and Constraints
***************************************************************************************************************
SQL>
SQL> DROP TABLE TBL_TRG
2 /
Table dropped.
SQL> CREATE TABLE TBL_TRG
2 (ID NUMBER, ID_TYPE VARCHAR2(100), EFF_DT TIMESTAMP,TMN_DT TIMESTAMP,
3 id_col1 number,id_col2 varchar2(100)
4 )
5 /
Table created.
***************************************************************************************************************
SQL>
SQL> CREATE OR REPLACE TRIGGER TRG_TEST_COMPOUND
2 FOR INSERT ON TBL_TRG
3 COMPOUND TRIGGER
4 V_TBL_TRG TBL_TRG%ROWTYPE;
5 V_COUNT INTEGER:=0;
6
7 BEFORE EACH ROW
8 IS
9 BEGIN
10 BEGIN
11 V_TBL_TRG.ID := :NEW.ID;
12 V_TBL_TRG.ID_TYPE := :NEW.ID_TYPE;
13 V_TBL_TRG.EFF_DT := :NEW.EFF_DT;
14 V_TBL_TRG.TMN_DT := :NEW.TMN_DT;
15 V_TBL_TRG.ID_COL1 := :NEW.ID_COL1;
16 V_TBL_TRG.ID_COL2 := :NEW.ID_COL2;
17 END;
18 END
19 BEFORE EACH ROW;
20
21 AFTER STATEMENT IS
22 BEGIN
23 SELECT COUNT(*) INTO V_COUNT
24 FROM TBL_TRG WHERE
25 ID = V_TBL_TRG.ID AND
26 ID_TYPE = V_TBL_TRG.ID_TYPE AND
27 ID_COL1 = V_TBL_TRG.ID_COL1 AND
28 ID_COL2 = V_TBL_TRG.ID_COL2 AND
29 TMN_DT = TO_TIMESTAMP('31-DEC-9999 12:00:00 AM');
30
31 DBMS_OUTPUT.PUT_LINE('Count ='||v_Count);
32
33 IF V_COUNT = 1 THEN
34
35 UPDATE TBL_TRG
36 SET TMN_DT = SYSTIMESTAMP WHERE
37 TMN_DT = TO_TIMESTAMP('31-DEC-9999 12:00:00 AM') AND
38 EFF_DT < V_TBL_TRG.EFF_DT AND
39 ID = V_TBL_TRG.ID AND
40 ID_TYPE = V_TBL_TRG.ID_TYPE
41 ;
42
43 ELSE
44 -- REPORT ISSUE
45 RAISE_APPLICATION_ERROR
46 (-20001, 'Mutiple Version of Data already Exist');
47
48 END IF;
49 END AFTER STATEMENT;
50 END;
51 /
Trigger created.
SQL> show error
No errors.
SQL> /
Trigger created.
SQL> INSERT INTO TBL_TRG
2 select level id,'A',systimestamp,TO_TIMESTAMP('31-DEC-9999 12:00:00 AM'),
3 LEVEL,'Oracle'||LEVEL FROM
4 DUAL CONNECT BY LEVEL <2
5 /
Count =1
1 row created.
SQL> INSERT INTO TBL_TRG
2 SELECT LEVEL+1 ID,'A',systimestamp,TO_TIMESTAMP('31-DEC-9999 12:00:00 AM'),
3 LEVEL+1,'Oracle'||(LEVEL+1) FROM
4 DUAL CONNECT BY LEVEL <2
5 /
Count =1
1 row created.
SQL> COMMIT
2 /
Commit complete.
SQL> SELECT * FROM TBL_TRG
2 /
ID ID_TYPE EFF_DT TMN_DT ID_COL1 ID_COL2
------ -------- ----------------------------- ---------------------------- --------- ----------
1 A 20-MAR-16 09.34.22.839115 PM 31-DEC-99 12.00.00.000000 AM 1 Oracle1
2 A 20-MAR-16 09.34.22.847461 PM 31-DEC-99 12.00.00.000000 AM 2 Oracle2
SQL>
SQL> ------Insert Duplicate Version---------
SQL> set serveroutput on
SQL> declare
2 my_exception exception;
3 pragma exception_init(my_exception,-20001);
4 begin
5
6 INSERT INTO TBL_TRG
7 select level id,'A',systimestamp,TO_TIMESTAMP('31-DEC-9999 12:00:00 AM'),
8 LEVEL,'Oracle'||LEVEL FROM
9 DUAL CONNECT BY LEVEL <2;
10 commit;
11 exception
12 WHEN MY_EXCEPTION THEN
13 dbms_output.put_line('Insert Ignored - Duplicate Version Avaliable');
14 rollback;
15 end;
16 /
Count =2
Insert Ignored - Duplicate Version Avaliable
PL/SQL procedure successfully completed.
SQL> SELECT * FROM TBL_TRG
2 /
ID ID_TYPE EFF_DT TMN_DT ID_COL1 ID_COL2
------ -------- ----------------------------- ---------------------------- --------- ----------
1 A 20-MAR-16 09.34.22.839115 PM 31-DEC-99 12.00.00.000000 AM 1 Oracle1
2 A 20-MAR-16 09.34.22.847461 PM 31-DEC-99 12.00.00.000000 AM 2 Oracle2
SQL> ------Insert New Version---------
SQL> declare
2 my_exception exception;
3 pragma exception_init(my_exception,-20001);
4 begin
5
6 INSERT INTO TBL_TRG
7 SELECT LEVEL ID,'A',SYSTIMESTAMP,TO_TIMESTAMP('31-DEC-9999 12:00:00 AM'),
8 LEVEL+1,'Oracle'||LEVEL FROM
9 DUAL CONNECT BY LEVEL <2;
10 commit;
11 exception
12 WHEN MY_EXCEPTION THEN
13 dbms_output.put_line('Insert Ignored - Duplicate Version Avaliable');
14 rollback;
15 end;
16 /
Count =1
PL/SQL procedure successfully completed.
SQL>
SQL> COMMIT
2 /
Commit complete.
SQL> SELECT * FROM TBL_TRG
2 /
ID ID_TYPE EFF_DT TMN_DT ID_COL1 ID_COL2
------ -------- ----------------------------- ---------------------------- --------- ----------
1 A 20-MAR-16 09.34.22.839115 PM 20-MAR-16 09.40.36.987940 PM 1 Oracle1
2 A 20-MAR-16 09.34.22.847461 PM 31-DEC-99 12.00.00.000000 AM 2 Oracle2
1 A 20-MAR-16 09.40.36.987276 PM 31-DEC-99 12.00.00.000000 AM 2 Oracle1
