+321 123 4567
info@test.com
User login
Facebook
Google+
puthranv-logo
  • Home
  • Profile
  • Talks
  • Videos
  • Contact

From_Mutating_Trigger_to_ Oracle 11g_Compound_Triggers_Part_2

March 20, 2011puthranvOracle, PLSQLNo Comments

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
Tags: PLSQL
puthranv
Previous post From Mutating Trigger to Oracle 11g Compound Triggers - Part 1 Next post Find bind variable values in oracle

Related Articles

From Mutating Trigger to Oracle 11g Compound Triggers - Part 1

February 24, 2011puthranv

PLSQL New Features in Oracle 11g Part 1

January 6, 2011puthranv

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Recent Posts

  • Locks in Oracle - Part 2 Copy
  • Oracle Partition Pruning on Date Comparisons
  • Date Filters and their impact over Index
  • Oracle 12c - Pattern Matching Part 1
  • Awarded - AIOUG Volunteer of the Year 2015 at Sangam15

Recent Comments

    Archives

    • May 2017
    • August 2016
    • July 2016
    • June 2016
    • November 2015
    • October 2015
    • January 2015
    • November 2014
    • March 2014
    • February 2014
    • January 2014
    • December 2013
    • April 2013
    • March 2013
    • February 2013
    • January 2013
    • December 2012
    • November 2012
    • October 2012
    • September 2012
    • August 2012
    • July 2012
    • June 2012
    • May 2012
    • April 2012
    • March 2012
    • February 2012
    • January 2012
    • March 2011
    • February 2011
    • January 2011

    Categories

    • Awards
    • Miscellaneous
    • Oracle
    • Performance Tuning
    • PLSQL
    • Replication
    • Speaking
    • SQL
    • Uncategorized

    Meta

    • Log in
    • Entries RSS
    • Comments RSS
    • WordPress.org

    Recent Posts

    • Locks in Oracle - Part 2 Copy May 8, 2017
    • Oracle Partition Pruning on Date Comparisons August 18, 2016
    • Date Filters and their impact over Index July 15, 2016
    • Oracle 12c - Pattern Matching Part 1 June 15, 2016
    December 2019
    M T W T F S S
    « May    
     1
    2345678
    9101112131415
    16171819202122
    23242526272829
    3031  

    Tags

    bind variable Cardinality Estimate Child Cursors Date Comparisons Date Filters design MEMBER OF Vs TABLE OF CAST music oracle Oracle 12c - Pattern Matching Oracle 12c - With Clause Enhancements Oracle 12c New Features photography Pipeline Functions PLSQL PLSQL index by table to Java/SQL Real Time SQL Monitoring Tuning video wordpress Zero Downtime Migration

    Categories

    • General (1)
      • Awards (1)
    • Oracle (28)
      • Miscellaneous (9)
      • Performance Tuning (11)
      • PLSQL (6)
      • Replication (1)
      • SQL (1)
    • Speaking (2)
    • Uncategorized (1)

    Text widget

    Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas sed diam eget risus varius blandit sit amet non magna. Mattis purus sit amet fermentum.

    Maecenas sed diam eget risus varius blandit sit amet non magna. Mattis purus sit amet fermentum.

    Mattis purus sit amet fermentum. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Maecenas sed diam eget risus varius blandit sit amet non magna.

    Contacts

    1600 Amphitheatre Parkway, Mountain View, CA 94043, United States
    +321 123 456 7
    info@example.com
    Facebook
    Vimeo
    Behance
    Instagram
    Xing
    SoundCloud
    HomeProfile
    © 2016 Puthranv by Casis