Compound Triggers |
About Compound triggers:
- First appeared in Oracle 11g.
- Can fire at multiple timing points.
- Each timing point section has its own executable parte and optional exection-handling part.
- Act as both statement- and row-level triggers.
- Can be defined on either a table or a view
- Do not support filtering actions with the WHEN clause or the use of the autonomous transaction PRAGMA
- The firing order of compound triggers is not guaranteed because they can be interleaved (mixed between) with the firing of standalone triggers.
- There is a declaration section for the trigger as a whole. Individual timing points share this single global declaration section, whose state is maintained for the lifetime of the statement. In addition, each timing point section has its own local declaration section.
- Compound triggers do not support an EXCEPTION block, but you can implement EXCEPTION
blocks in any of the subordinate timing point blocks. - Compound triggers do not fire when (a) the DML statement doesn’t change any rows and (b) the trigger does not implement at least a BEFORE STATEMENT or AFTER STATEMENT block.
- In previous releases this type of functionality was only possible by defining multiple triggers whose code and global variables were defined in a separate package.
Common uses of Compound DML Triggers:
- Accumulate rows - with values obtained or generated at each "EACH ROW" firing - so that these can be inserted with BULK-INSERT
- To avoid the mutating-table error (
ORA-04091
)
Restrictions:
OLD
,NEW
, andPARENT
cannot appear in the declarative part, theBEFORE
STATEMENT
section, or theAFTER
STATEMENT
section.- Only the
BEFORE
EACH
ROW
section can change the value ofNEW
. - A timing-point section cannot handle exceptions raised in another timing-point section.
- If a timing-point section includes a
GOTO
statement, the target of theGOTO
statement must be in the same timing-point section.
Syntax:
CREATE [OR REPLACE] TRIGGER trigger_name FOR {INSERT | UPDATE | UPDATE OF column1 [, column2 [, column(n+1)]] | DELETE} ON table_name COMPOUND TRIGGER [BEFORE STATEMENT IS [declaration_statement;] BEGIN execution_statement; END BEFORE STATEMENT;] [BEFORE EACH ROW IS [declaration_statement;] BEGIN execution_statement; END BEFORE EACH ROW;] [AFTER EACH ROW IS [declaration_statement;] BEGIN execution_statement; END AFTER EACH ROW;] [AFTER STATEMENT IS [declaration_statement;] BEGIN execution_statement; END AFTER STATEMENT;] END [trigger_name]; /
Mutating-Table error: what is it? |
- A mutating table is a table that is being modified by a DML statement (i.e. by the effects of a DELETE CASCADE constraint).
- If a trigger is fired as the result of a DML statement in table A, it cannot query or modify table A (the table that the triggering statement is modifying.)
- When a row-level trigger encounters a mutating table, ORA-04091 occurs, the effects of the trigger and triggering statement are rolled back, and control returns to the user or application that issued the triggering statement.
Example:
(a) create table ELOG to log updates in the table EMPLOYEES
(b) create trigger to insert into ELOG the values (:OLD) that are modified when an UPDATE is issued against EMPLOYEES.
(c) The trigger should (1) Insert the OLD values into ELOG and (2) Print a statement with the number of rows remaining on EMPLOYEES.
- For this, it has to perform a SELECT in the table that is being modified (EMPLOYEES). And there the bicho pega...
create table elog ( emp_id number(6), l_name varchar2(25), f_name varchar2(20)); create or replace trigger log_deletions after delete on employees for each row declare n integer; begin insert into log values (:old.employee_id, :old.last_name, :old.first_name); select count(*) into n from employees; dbms_output.put_line('there are now ' || n || ' employees.'); end; delete from employees where employee_id=197; SQL Error: ORA-04091: table EMPLOYEES is mutating, trigger/function may not see it ORA-06512: at "LOG_DELETIONS", line 8 ORA-04088: error during execution of trigger 'LOG_DELETIONS' 04091. 00000 - "table %s.%s is mutating, trigger/function may not see it" *Cause: A trigger (or a user defined plsql function that is referenced in this statement) attempted to look at (or modify) a table that was in the middle of being modified by the statement which fired it. *Action: Rewrite the trigger (or function) so it does not read that table.
(?) How to solve this error?
(1) You may Use a temporary table
Instead of using one AFTER each row trigger that updates the mutating table, use two triggers
(a) an AFTER each row trigger that updates the temporary table and
(b) an AFTER statement trigger that updates the mutating table with the values from the temporary table.
(2) Use a compound DML trigger:
Compound Trigger: Bulk Logging updates to solve ORA-04091 |
(a) create table emp_log_update
(b) create sequence emp_log_update_seq
(c) Create trigger emp_log_tg
(d) Configure client information (use
sys_context
to set client info) (e) Update rows on employees to fire the compound trigger
(f) check the inserted row on emp_log_update
-- create table to log updates. The table will be used by the compound trigger. create table emp_log_update (emp_log_id number, emp_id number, oldval varchar2(20), newval varchar2(20), created_by varchar2(20), creation_date date, last_updated_by varchar2(20), last_update_date date); create sequence emp_log_update_seq; -- create compound trigger. -- The For each row section: stores :old and :new values for each updated row -- The statement section: performs a bulk insert in the log table. CREATE OR REPLACE TRIGGER employee_update_tg FOR UPDATE of SALARY on EMPLOYEES COMPOUND TRIGGER TYPE emp_rec IS RECORD (emp_log_id emp_log_update.emp_log_id%type, emp_id emp_log_update.emp_id%type, oldval emp_log_update.oldval%type, newval emp_log_update.newval%type, created_by emp_log_update.created_by%type, creation_date emp_log_update.creation_date%type, last_updated_by emp_log_update.last_updated_by%type, last_update_date emp_log_update.last_update_date%type); TYPE emp_list IS TABLE OF emp_rec; emp_updates emp_list := emp_list(); BEFORE EACH ROW IS -- declare local timing point variables c number; -- stores the client info (identification, set up in the application) user_id varchar2(20) := nvl((sys_context('userenv','client_info')), -1); BEGIN emp_updates.EXTEND; c := emp_updates.LAST; emp_updates(c).emp_log_id := emp_log_update_seq.nextval; emp_updates(c).emp_id := :old.employee_id; emp_updates(c).oldval := to_char(:old.salary); emp_updates(c).newval := to_char(:new.salary); emp_updates(c).created_by := user_id; emp_updates(c).creation_date := sysdate; emp_updates(c).last_updated_by := user_id; emp_updates(c).last_update_date := sysdate; END BEFORE EACH ROW; AFTER STATEMENT IS BEGIN FORALL i in emp_updates.FIRST..emp_updates.LAST insert into emp_log_update values ( emp_updates(i).emp_log_id, emp_updates(i).emp_id, emp_updates(i).oldval, emp_updates(i).newval, emp_updates(i).created_by, emp_updates(i).creation_date, emp_updates(i).last_updated_by, emp_updates(i).last_update_date ); END AFTER STATEMENT; END; Trigger created. -- Set the client info: SQL> EXEC dbms_application_info.set_client_info('dev2'); PL/SQL procedure successfully completed. SQL>update employees set salary=salary*1.15 where department_id=60; 5 rows updated. SQL> select * from emp_event_log; (formatted result..) ID EMP_ID OLDVAL NEWVAL CREATED_BY CREATION LAST_UPDATED LAST_UPDATED DATE BY DATE --- ------- --------- -------- ------------ --------- ------------- ------------ 2 103 9000 10350 dev2 26-AUG-11 dev2 26-AUG-11 3 104 6000 6900 dev2 26-AUG-11 dev2 26-AUG-11 4 105 4800 5520 dev2 26-AUG-11 dev2 26-AUG-11 5 106 4800 5520 dev2 26-AUG-11 dev2 26-AUG-11 6 107 4200 4830 dev2 26-AUG-11 dev2 26-AUG-11
DBMS_APPLICATION_INFO package |
---|
#set module and transaction name CREATE or replace PROCEDURE add_employee( name VARCHAR2, salary NUMBER, manager NUMBER, title VARCHAR2, commission NUMBER, department NUMBER) AS BEGIN DBMS_APPLICATION_INFO.SET_MODULE( module_name => 'add_employee', action_name => 'insert into emp'); INSERT INTO emp (ename, empno, sal, mgr, job, hiredate, comm, deptno) VALUES (name, emp_seq.nextval, salary, manager, title, SYSDATE, commission, department); DBMS_APPLICATION_INFO.SET_MODULE(null,null); END; # Set transaction name CREATE OR REPLACE PROCEDURE bal_tran (amt IN NUMBER(7,2)) AS BEGIN -- balance transfer transaction DBMS_APPLICATION_INFO.SET_ACTION( action_name => 'transfer from chk to sav'); UPDATE chk SET bal = bal + :amt WHERE acct# = :acct; UPDATE sav SET bal = bal - :amt WHERE acct# = :acct; COMMIT; DBMS_APPLICATION_INFO.SET_ACTION(null); END; Other example: Session 1 (anonymous block): (a) set action and client info. (b) delete rows from employees Session 2: (a) query V$SESSION #SID 38: SQL> BEGIN dbms_application_info.set_action(action_name => 'delete from temp_emp'); dbms_application_info.set_client_info(client_info => 'Issued from terminal 2'); delete from temp_emp where city='Denver'; END; SQL> / PL/SQL procedure successfully completed #SID 2: SQL> select sid, serial#, username, osuser, module, action, client_info from v$session; (formatted output) SID SERIAL# USERNAME OSUSER MODULE ACTION CLIENT_INFO ---- -------- --------- -------- ---------- --------------------- ---------------------- 2 10 SYSTEM jdba SQL*Plus (...) 38 7 JFARR jdev SQL*Plus delete from temp_emp Issued from terminal 2 (...) |
No comments:
Post a Comment