(ref) On Compound Triggers

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, and PARENT cannot appear in the declarative part, the BEFORE STATEMENT section, or the AFTER STATEMENT section.
  • Only the BEFORE EACH ROW section can change the value of NEW.
  • 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 the GOTO 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
The example below uses a compound trigger to log salary updates in the employees table, and capture old and new values for the updated columns.
(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
  • Use DBMS_APPLICATION_INFO.{SET_CLIENT_INFO | READ_CLIENT_INFO} to maintain client info
  • Read/Write column CLIENT_INFO on V$SESSION and V$SQL_AREA
  • You can set module name or (trans)action name.
  • Also useful with Oracle Trace and SQL Trace: to record names of modules or transactions for performance analysis or debugging.
  • set action name before the start of a transaction.
  • DBMSAPIN.SQL run by catproc

#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