'Oracle11g'에 해당되는 글 1건

  1. 2012.07.26 Compound Triggers in Oracle 11g

Compound Triggers in Oracle 11g

Oracle 2012. 7. 26. 13:19

Oracle 11g에서는 Compound Triggers 기능이 생겨서 각 Event별로 Trigger를 만들 필요가 없어졌네요.

 

In earlier versions of Oracle, triggers were the database objects whose ability to collide with the current database state of an object and session state maintenance, kept the database developers alert while designing triggers.  Oracle11g offers considerable improvements in database triggers. These additions have not only enhanced the language usability but also promise a better performance in a real application environment. 

Oracle 11g fixes major issues by the enhancements as listed below.

  • Trigger firing sequence can be set in Oracle 11g using FOLLOWS keyword
  • Compound Triggers to encapsulate multiple triggering timings in one body
  • Triggers can be created in ENABLED/DISABLED mode
  • The DML triggers in 11g are 25% faster than their earlier versions in terms of compilation, execution and firing

In the tutorial, you will learn how Compound Triggers works and its benefits to the users.

1. Compound Triggers: Introduction

Compound triggers are the database DML triggers which ‘compounds’ or combines all the triggering timings under one trigger body. The triggering timings available for database triggers are BEFORE STATEMENT, AFTER STATEMENT, BEFORE EACH ROW, and AFTER EACH ROW. Trigger level variables can be defined which would be visible and accessible in all the timing blocks.

Compound trigger body does not contain any exception section but recommends each block to contain its own exception handler under exception section.

2. Compound Triggers: Reaping the Benefits

Compound trigger resolves few big issues, which have been the nightmare for the developers in the past.

  1. Mutating table error ORA-04091
  2. Multithread behavior maintains persistent state of session variables till the statement execution finishes. These are defined in the declaration section of the compound trigger.
  3. Enhanced performance in bulk operations
  4. Supports encapsulation of multiple program units; thus enhances code interactivity

3. Compound Triggers: Usage Guidelines

As a new induction to the database family, there are some introductory guidelines which must be followed while working with compound triggers

  • Compound trigger are meant only for DML operations. No support still for DDL and system operations.
  • Exception handling process has to be done for each timing block.
  • Compound trigger remains passive, if the DML does not changes any rows
  • :OLD and :NEW variable identifiers can exist only in ROW level blocks, where :NEW values can only be modified in BEFORE EACH ROW block
  • No support for WHEN clause. Earlier, WHEN clause could be utilized to impose condition on the trigger action.
  • No support for PRAGMA_AUTONOMOUS_TRANSACTION

There are many debates over the above guidelines, which many identify as compound trigger restrictions. But from developer’s perspective, the benefits from compound triggers are much heavier than these restrictions. The assumptions and restrictions can be melted into code practice and as ‘usage guidelines’.

4. Compound Triggers: Syntax

The syntax shows the four timing blocks in a set order.

Example Syntax [1a]: For DML Triggers

Sample Code
  1. CREATE OR REPLACE TRIGGER [TRIGGER NAME]
  2. FOR [DML] ON [TABLE NAME]
  3. COMPOUND TRIGGER
  4. -- Initial section 
  5. -- Declarations 
  6. -- Subprograms
  7. Optional section
  8. BEFORE STATEMENT IS
  9. …;
  10. Optional section
  11. AFTER STATEMENT IS
  12. …;
  13. Optional section
  14. BEFORE EACH ROW IS
  15. …;
  16. Optional section
  17. AFTER EACH ROW IS
  18. …;
  19. END; 
Copyright exforsys.com


In the syntax, note that the compound trigger offers only the declaration section, which are again locally available in the timing blocks.

Example Syntax [1b]: For database views

INSTEAD OF EACH ROW IS 
...; 
END;

Note that none of the timing blocks should be duplicated. Oracle server raises exception PLS-00676 if duplicate timing blocks is found in the compound trigger definition.

5. Compound Triggers: Additional Notes

5.1. USER_TRIGGERS dictionary view structure has been restructured to include the metadata for compound triggers. The new columns specify whether a timing block is available in the compound trigger body.

5.2. The new triggering event has been introduced for compound trigger as ‘COMPOUND’. The figure below shows the TRIGGER_TYPE, TRIGGERING_EVENT and TABLE_NAME of the trigger TRG_COMP_DEMO.

5.3. The columns BEFORE_STATEMENT, BEFORE_ROW, AFTER_ROW, and AFTER_STATEMENT are set as YES/NO based on the timing blocks available in the compound trigger. For example, the trigger TRG_COMP_DEMO contains all the timing blocks in the trigger body, the columns can be queried as below.

6. Applications of Compound Triggers

A compound trigger is best suited to achieve two objectives.

  • Yield better performance while loading a table simultaneous to a running transaction and using its values.
  • Resolve mutating table error (ORA-04091)

I will  illustrate both the accomplishments in below steps

6.1. Demonstrating performance gains during simultaneous loading

1. Two tables were created

Sample Code
  1. SQL> CREATE TABLE ORDERS (ORD_ID NUMBER, 
  2. ITEM_CODE VARCHAR2(100), 
  3. ORD_QTY NUMBER, 
  4. ORD_DATE DATE); 
  5. TABLE created. 
  6. SQL> CREATE TABLE ORDER_ARCHIVE(ORD_ID NUMBER, 
  7. ORD_CODE VARCHAR2(100));
  8.  
  9. TABLE created. 
Copyright exforsys.com


2. To demonstrate the performance difference between the versions, I create a normal FOR EACH ROW trigger on ORDERS table to insert data into ORDER_ARCHIVE table.

Sample Code
  1. SQL> CREATE OR REPLACE TRIGGER TRG_ORDERS
  2. BEFORE INSERT ON ORDERS
  3. FOR EACH ROW 
  4. BEGIN
  5. DBMS_OUTPUT.PUT_LINE('Insert order‘||:NEW.ORD_ID||’ into ORDER_ARCHIVE');
  6. INSERT INTO ORDER_ARCHIVE VALUES (:NEW.ORD_ID,:NEW.ITEM_CODE);
  7. END;
  8. / 
  9. TRIGGER created. 
Copyright exforsys.com


3. Now, I will insert the test data into the ORDERS table using SELECT statement

Sample Code
  1. SQL> INSERT INTO orders(ord_id, item_code, ord_date)
  2. 2 SELECT (rownum+1)*100, 'ITEM_'||rownum, sysdate-rownum
  3. 3 FROM DUAL
  4. 4* CONNECT BY ROWNUM < 15; 
  5. INSERT ORDER 1 INTO ORER_ARCHIVE
  6. INSERT ORDER 2 INTO ORER_ARCHIVE
  7. INSERT ORDER 3 INTO ORER_ARCHIVE
  8. INSERT ORDER 4 INTO ORER_ARCHIVE
  9. INSERT ORDER 5 INTO ORER_ARCHIVE
  10. INSERT ORDER 6 INTO ORER_ARCHIVE
  11. INSERT ORDER 7 INTO ORER_ARCHIVE
  12. INSERT ORDER 8 INTO ORER_ARCHIVE
  13. INSERT ORDER 9 INTO ORER_ARCHIVE
  14. INSERT ORDER 10 INTO ORER_ARCHIVE
  15. INSERT ORDER 11 INTO ORER_ARCHIVE
  16. INSERT ORDER 12 INTO ORER_ARCHIVE
  17. INSERT ORDER 13 INTO ORER_ARCHIVE
  18. INSERT ORDER 14 INTO ORER_ARCHIVE 
  19. 14 rows created. 
Copyright exforsys.com


Note the output of the above INSERT process. For each of the 14 records inserted into the ORDERS table through the trigger, Oracle server makes simultaneous inserts into the ORDER_ARCHIVE table.

4. Compound triggers in Oracle 11g have the ability to perform simultaneous bulk insert process into the table. I will create the compound trigger TRG_COMP_ORDERS to implement the bulk loading process. Note the use of associative arrays, which hold the data to be inserted. It flushes off once the data is replicated into the ORDER_ARCHIVE table when the index count reaches 20 or the statement execution completes.

Sample Code
  1. SQL> CREATE OR REPLACE TRIGGER TRG_COMP_SAL
  2. FOR INSERT ON ORDERS
  3. COMPOUND TRIGGER
  4. TYPE ORDER_T IS TABLE OF ORDER_ARCHIVE%ROWTYPE 
  5. INDEX BY PLS_INTEGER;
  6. L_ORDERS ORDER_T;
  7. I NUMBER := 0;
  8. AFTER EACH ROW IS
  9. BEGIN
  10. I := I+1;
  11. L_ORDERS(I).ORD_ID := :NEW.ORD_ID;
  12. L_ORDERS(I).ORD_CODE := :NEW.ITEM_CODE;
  13. IF I >= 20 THEN
  14. DBMS_OUTPUT.PUT_LINE('Bulk Load for 20 orders'); 
  15. FOR J IN 1..I
  16. LOOP
  17. INSERT INTO ORDER_ARCHIVE VALUES L_ORDERS(J);
  18. END LOOP;
  19. L_ORDERS.DELETE;
  20. I := 0;
  21. END IF;
  22. END AFTER EACH ROW;
  23. AFTER STATEMENT IS
  24. BEGIN
  25. DBMS_OUTPUT.PUT_LINE('Statement level loading');
  26. FORALL J IN 1..L_ORDERS.COUNT
  27. INSERT INTO ORDER_ARCHIVE VALUES L_ORDERS(J);
  28. L_ORDERS.DELETE;
  29. I := 0;
  30. END AFTER STATEMENT;
  31. END;
  32. /
  33.  
  34. TRIGGER created. 
Copyright exforsys.com


5. I will insert 64 rows (instead of just 14) into ORDERS table using SELECT query. The simultaneous inserts into ORDER_ARCHIVE table are achieved only in 3 bulk insert process.

Sample Code
  1. SQL> INSERT INTO orders(ord_id, item_code, ord_date)
  2. 2 SELECT (rownum+1)*100, 'ITEM_'||rownum, sysdate-rownum
  3. 3 FROM DUAL
  4. 4 connect BY rownum < 65
  5. 5 / 
  6. Bulk LOAD FOR 20 orders
  7. Bulk LOAD FOR 20 orders
  8. Bulk LOAD FOR 20 orders
  9. Statement level loading 
  10. 64 rows created. 
Copyright exforsys.com


Above results clearly differentiate the compound trigger from the conventional triggers in terms of performance while simultaneous data loading.

6.2. Demonstration of Mutating table solution using Compound triggers

Now, let us learn about the most accomplished achievement of Compound Triggers, i.e. their ability to tackle with Mutating table error (ORA-04091).

Mutating table occurs when a table is referenced when it is in floating state. A table is in flux or floating state when it is the participant of a running transaction. Earlier, it used to be taken as a precaution during coding or its effects were diluted using workaround solutions. Few of the efficient workarounds are as below.

  • Change in logic implementation and code design
  • Using PRAGMA_AUTONOMOUS_TRANSACTION
  • Conversion of row level trigger to statement level
  • Defining package to hold variables at session level; these variables would hold the values and later would be inserted once the statement execution finishes.

How compound trigger fixes the problem?

Compound trigger contains the timing blocks and variables, which are persistent till the statement completes and are visible in all the timing blocks of the trigger. These variables are of collection type which holds the table data, before it enters the flux mode. Table gets locked once it moves to flux mode, but the backup data in the collection variables remains persistent and can be used for reference within the trigger.

Therefore, the logic used is same as earlier, but being a single compiled unit, it is more convenient and easily maintained.

In the example code below, the compound trigger fires on update of ORDERS table. It queries the ORDERS table to fetch and display the old quantity. For a conventional DML trigger, the situation is the best candidate for ORA-04091 Mutating table error.

A user updates the order quantity for order id 600. Now before the UPDATE statement executes, the BEFORE STATEMENT block fetches the old ‘order quantity’ value (90) and stores in a collection type variable. Now when the statement executes, it updates the ‘order quantity’ to 150. AFTER STATEMENT block in the trigger displays the ‘old quantity’ value.

Sample Code
  1. CREATE OR REPLACE TRIGGER TRG_ORDERS
  2. FOR UPDATE ON ORDERS
  3. COMPOUND TRIGGER
  4. TYPE ORD_QTY_T IS TABLE OF ORDERS.ORD_QTY%TYPE;
  5. L_ORD_QTY ORD_QTY_T; 
  6. BEFORE STATEMENT IS
  7. BEGIN 
  8. SELECT ORD_QTY 
  9. BULK COLLECT INTO L_ORD_QTY 
  10. FROM ORDERS WHERE ORD_ID=600; 
  11. END BEFORE STATEMENT; 
  12. AFTER EACH ROW IS
  13. BEGIN
  14. DBMS_OUTPUT.PUT_LINE('Old Quantity Value:'||L_ORD_QTY(L_ORD_QTY.FIRST));
  15. END AFTER EACH ROW; 
  16. END;
  17. / 
  18. SQL> UPDATE ORDERS
  19. 2 SET ORD_QTY = 150
  20. 3 WHERE ORD_ID = 600;
  21. Old Quantity Value:90 
  22. 1 row updated. 
Copyright exforsys.com


7. Conclusion

Compound trigger combines the properties of both statement and row level triggers. The logic which was earlier maintained at row and statement level can now be placed into one single body, with state and persistency in data across the timings.

 

출처 : http://www.exforsys.com/tutorials/oracle-11g/compound-triggers-in-oracle-11g.html

저작자표시 비영리 동일조건

'Oracle' 카테고리의 다른 글

Oracle PL/SQL 기초 교육 문서  (0) 2013.03.06
Oracle Data Record별 Text File 내보내는 방법  (1) 2013.01.21
ORA-20023: Missing start and end values for time model stat  (0) 2012.05.15
Bug 5969934: EXPDP CLIENT GETS UDE-00008 ORA-31626 WHILE THE SERVER SIDE EXPORT IS OK  (4) 2011.08.11
DataPump Export (EXPDP) Client Gets UDE-8 ORA-31626 ORA-39086 [ID 549781.1]  (3) 2011.08.11
:     

TISTORY에 Login하려면 여기를 누르세요.

◀ PREV : [1] : NEXT ▶

티스토리툴바