Oracle Illustrated

Feb 21 2010   8:41AM GMT

Migrating from 9i to 11g – Trigger Enhancements



Posted by: Lakshmi Venkatesh
Tags:

Oracle trigger enhancements are quite exciting !!

1 DML Trigger – faster
With Oracle 11g DML triggers are much faster when the ROW-LEVLE triggers perform operations on other tables. Documentation suggest that it offers noticable performance improvements in several cases.

Scripts for testing trigger enhancements-

Create table emp_audit (username varchar2(200), operation varchar2(100), sys_time date)
/

CREATE OR REPLACE TRIGGER EMP_TRIG2
BEFORE INSERT OR UPDATE ON emp
referencing new as new old as old
for each row
begin
INSERT INTO emp_audit VALUES (user, ‘INSERT’, sysdate);
end;
/

Oracle 9i
SQL> update emp set comm = comm *1.5;

40541 rows updated.

Elapsed: 00:00:10.28

Oracle 11g
SQL> update emp set comm = comm *1.5;

40541 rows updated.

Elapsed: 00:00:08.17

**********************************************************************************
2 Trigger – Compound

Usually when we perform bulk Insert / Update / Merge into a table and if there is a ROW LEVEL trigger on the table that performs DML operations into another table then, for each and every row trigger would be fired. This makes the very concept of bulk DML operations as record based operation internally. This problem can now be well handled using the compound triggers.

Another use of compound trigger is the triggering timing is well managed. Sometimes we may need to have after insert, before update, after each row, before statement etc., kind of different triggers and in most of the cases we are not sure that which trigger is executed first. But, with this compound triggers we can have all the triggers created in a single trigger in the sequence which we want them to be executed.

create table test_compound_trig (a number, b number)
/

create table test_compound_audit (a number, b number, c date)
/

1. Quick demo to show in what order the trigger is executed

CREATE OR REPLACE TRIGGER trig_test_timing FOR INSERT OR UPDATE OF a
ON test_compound_trig
COMPOUND TRIGGER
BEFORE EACH ROW IS
BEGIN
dbms_output.put_line(‘Now before each row is executed’);
END BEFORE EACH ROW;
BEFORE STATEMENT IS
BEGIN
dbms_output.put_line(‘Now the before statement is executed’);
END BEFORE STATEMENT;
AFTER STATEMENT IS
BEGIN
dbms_output.put_line(‘Now the after statement is executed’);
END AFTER STATEMENT;
AFTER EACH ROW IS
BEGIN
dbms_output.put_line(‘Now after each row is executed’);
END AFTER EACH ROW;
END trig_test_timing;

SQL> insert into test_compound_trig values (1,2);

SQL> insert into test_compound_trig select
2 rownum, rownum+1 from dual connect by level insert into test_compound_trig select
rownum, rownum+1 from dual connect by level < = 20;

Fires AN AFTER STATEMENT trigger

The above represents – for inserting 20 rows a STATEMENT LEVEL trigger is executed only once – this is an existing case that we already know. What is so new about ORACLE 11G COMPOUND TRIGGERS – say, on a table if we have a TRIGGER AFTER EACH ROW that inserts into an audit table – we can make it as compound trigger with BEFORE EACH ROW + STATEMENT LEVEL trigger and make the insert statement execute only once.

drop trigger only_statement_level;

drop trigger before_insert_trig;

If there are multiple triggers on the table – ROW LEVEL & STATEMENT LEVEL and if ROW LEVEL performs DML operations on tables then we can assign the values to collection in ROW LEVEL and perform the actual DML operations in STATEMENT level trigger.

Oracle 9i
create table test_compound_trig (a number, b number)
/

create table test_compound_audit (a number, b number, c date)
/

set serveroutput on size unlimited

CREATE OR REPLACE TRIGGER before_insert_trig
AFTER INSERT OR UPDATE ON test_compound_trig
FOR EACH ROW
BEGIN
INSERT INTO test_compound_audit values (1, 2, sysdate);
dbms_output.put_line(‘Fires for each row!!’);
END;
/

SQL> insert into test_compound_trig select
rownum, rownum+1 from dual connect by level select count(1) from test_compound_audit;

COUNT(1)
———-
20

Oracle 11g

1. PERFORMANCE OF DML operations inside TRIGGERS + Use of SEQUENCE directly inside the block

create table test_compound_trig (a number, b number)
/

create table test_compound_audit (a number, b number, c date)
/

set serveroutput on size unlimited

CREATE OR REPLACE TRIGGER trig_compound FOR INSERT OR UPDATE OF a
ON test_compound_trig
COMPOUND TRIGGER
type tp_audit is table of test_compound_audit%rowtype index by pls_integer;
tp_audit_tab tp_audit;
counter number := 0;
BEFORE EACH ROW IS
BEGIN
counter := counter + 1;
tp_audit_tab(counter).a := dummy_seq.nextval;
tp_audit_tab(counter).b := dummy_seq.currval;
tp_audit_tab(counter).c := sysdate;
dbms_output.put_line(‘Now before each row is executed’);
END BEFORE EACH ROW;
AFTER STATEMENT IS
BEGIN
dbms_output.put_line(‘Now the after statement is executed’);
FORALL i IN tp_audit_tab.first .. tp_audit_tab.last
INSERT INTO test_compound_audit values tp_audit_tab(i);
END AFTER STATEMENT;
END ;
/

SQL> insert into test_compound_trig select
2 rownum, rownum+1 from dual connect by level select count(1) from test_compound_audit;

COUNT(1)
———-
20

** This is particularly useful when we perform real BULK INSERTS !!
**********************************************************************************

3 Trigger – Follows clause

2. Controlling the order of execution of trigger

CREATE OR REPLACE TRIGGER foll1_trig
BEFORE INSERT ON test_compound_trig
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line(‘foll1_trig – executed FIRST’);
END;
/

CREATE OR REPLACE TRIGGER foll2_trig
BEFORE INSERT ON test_compound_trig
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line(‘foll1_trig – executed NEXT’);
END;
/

SQL> insert into test_compound_trig select
rownum, rownum+1 from dual ;
foll1_trig – executed NEXT
foll1_trig – executed FIRST

1 row created.

CREATE OR REPLACE TRIGGER foll1_trig
BEFORE INSERT ON test_compound_trig
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line(‘foll1_trig – executed FIRST’);
END;
/

CREATE OR REPLACE TRIGGER foll2_trig
BEFORE INSERT ON test_compound_trig
FOR EACH ROW
FOLLOWS foll1_trig
BEGIN
DBMS_OUTPUT.put_line(‘foll1_trig – executed NEXT’);
END;
/

SQL> insert into test_compound_trig select
2 rownum, rownum+1 from dual ;
foll1_trig – executed FIRST
foll1_trig – executed NEXT

**********************************************************************************

4 Trigger – Enable / Disable

3. Creating disabled / enabled triggers

Main use of creating ENABLED / DISABLED trigger – Sometimes, we write a complicated trigger – and may like to test it before enabling it – so we can possibly create a disable trigger first then once we know its functionally correct we can enable the same.

drop trigger foll2_trig

drop trigger foll1_trig

CREATE OR REPLACE TRIGGER enable_trig
BEFORE INSERT ON test_compound_trig FOR EACH ROW
ENABLE
BEGIN
DBMS_OUTPUT.put_line(‘ENABLE TRIGGER’);
END;
/

CREATE OR REPLACE TRIGGER disable
BEFORE INSERT ON test_compound_trig FOR EACH ROW
DISABLE
BEGIN
DBMS_OUTPUT.put_line(‘DISABLE TRIGGER’);
END;
/

SQL> drop table test_compound_trig;

SQL> drop table test_compound_audit;

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: