New Trigger Features - Oracle 11g

There are three major improvements to triggers in Oracle Database 11g.You now have some control over the order of triggers firing; you can create triggers ENABLED or DISABLED; and there is a new type of trigger,the compound trigger,that maintains a common state over the life of a DML operation.

Trigger Ordering: The FOLLOWS Clause

When creating a trigger,a new clause may be included in the CREATE TRIGGER statement. Here is a snippet showing the new FOLLOWS clause:

CREATE TRIGGER B4I_DONATIONS_JOU BEFORE INSERT ON DONATIONS
FOLLOWS B4I_DONATIONS_VAL,B4I_DONATIONS_SEC
WHEN ...

This new clause instructs Oracle to call B4I_DONATIONS_JOU after calling the two other triggers listed in the FOLLOWS clause (B4I_DONATIONS_VAL, B4I_DONATIONS_SEC).

Creating Triggers As ENABLED or DISABLED

Two new keywords,ENABLED and DISABLED,have been added to the trigger creation statement.These mutually exclusive keywords appear immediately before the optional WHEN clause and after the optional FOLLOWS clause. Here’s the B4I_DONATIONS_JOU trigger created initially disabled:

CREATE TRIGGER B4I_DONATIONS_JOU BEFORE INSERT ON DONATIONS
FOLLOWS B4I_DONATIONS_VAL,B4I_DONATIONS_SEC
DISABLED
WHEN ...

Compound Triggers Type

In prior versions of Oracle, to maintain and share data between different triggers on the same table, a handler package was usually employed to contain global variables holding the “state” for the particular DML operation underway.

Although this did work, the fact that the state stored in the package was not bound to the statement being executed meant that the developer had to ensure that the state was initialized in a BEFORE statement trigger and that any resources consumed, such as collections and cursors, were freed in an AFTER statement trigger.

Exceptions,of course, would cause the AFTER statement trigger to not be executed,and any significant resources normally freed by the AFTER statement trigger could be hung up in the trigger’s handler package until the BEFORE statement trigger was called again or the session terminated.A new type of trigger has been introduced in Oracle Database 11g,the compound trigger,to overcome these shortcomings of what are now known as simple triggers.

Compound triggers differ from simple triggers in that the compound trigger implements all of the timing-point logic (before statement, before each row, after each row, after statement) within its body,and all of those sections share the same set of variables declared in the trigger’s common declaration section.

The following example shows a compound trigger that collects donor names and amounts as part of a telethon system.At the end of the DML statement,a call to a package LIVE_FEED is called, resumably to update the totals board and maybe add the donor to a crawl on the bottom of the television screen.

create sequence donations_s
/
create table donations
( donation_id INTEGER NOT NULL PRIMARY KEY
,donor_name VARCHAR2(256) NOT NULL
,donation_amount NUMBER NOT NULL
,address_1 VARCHAR2(256) NOT NULL
,address_2 VARCHAR2(256)
,address_3 VARCHAR2(256)
,city VARCHAR2(80) NOT NULL
,state VARCHAR2(2) NOT NULL
,postal_code VARCHAR2(80)
,country VARCHAR2(2)
,phone VARCHAR2(80)
,email VARCHAR2(256)
)
/
CREATE OR REPLACE TRIGGER donation_ins_trg
FOR INSERT ON donations
COMPOUND TRIGGER
livePipe LIVE_FEED.pipe_t;
messQueue LIVE_FEED.messTab_t:=LIVE_FEED.messTab_t();
BEFORE STATEMENT IS
BEGIN
livePipe:=LIVE_FEED.getPipe();
EXCEPTION
WHEN NO_DATA_FOUND THEN
livePipe:=NULL;
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
IF :new.donation_amount<=0 THEN
RAISE_APPLICATION_ERROR(-20001,'Donations must be positive');
END IF;
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
IF livePipe IS NOT NULL THEN
messQueue.EXTEND;
messQueue(messQueue.LAST).donor_name:=:new.donor_name;
messQueue(messQueue.LAST).donation_amount:=:new.donation_amount;
END IF;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
IF livePipe IS NOT NULL THEN
LIVE_FEED.enqueue(livePipe,messQueue);
END IF;
END AFTER STATEMENT;
END donation_ins_trg;

This code collects donor/amount pairs in the messQueue collection.When the after trigger is called,the LIVE_FEED.enqueue() procedure is called.If a negative donation amount, or some table constraint is violated, the after trigger is not called, except in certain cases with FORALL that will be covered later.

The following example shows a somewhat stubby implementation of LIVE_FEED:

CREATE OR REPLACE
package LIVE_FEED as
SUBTYPE pipe_t IS PLS_INTEGER;
TYPE messRec_t IS RECORD(donor_name VARCHAR2(256),donation_amount NUMBER);
TYPE messTab_t IS TABLE OF messRec_t;
FUNCTION getPipe RETURN pipe_t;
PROCEDURE enqueue(pPipe pipe_t,messArr messTab_t);
end;
/
CREATE OR REPLACE
package body LIVE_FEED IS
FUNCTION getPipe RETURN pipe_t IS
BEGIN
RETURN 14553;
END;
PROCEDURE enqueue(pPipe pipe_t,messArr messTab_t) IS
BEGIN
FOR i IN 1..messArr.COUNT LOOP
dbms_output.put_line('Donor: '||messArr(i).donor_name
||' Amount: '||messArr(i).donation_amount);
END LOOP;
END;
END;
/

That’s pretty simple—the example just uses DBMS_OUTPUT to list the enqueued records.The following is a test of the trigger using a simple insert statement:

insert into donations
select 1,'John Q Public',100,'101 Main St.'
,null,null,'Anytown','ST','0000000','US','111-555-3333','jqpublic@email.net'
from dual
union all
select 2,'Percival Lowell',200,'100 Cactus Ln'
,null,null,'Flagstaff','AZ','86001','US','928-555-0000','plowell@email.net'
from dual
union all
select 3,'Jane Doe',400,'101 Elm'
,null,null,'Peaceville',null,'0000000','US','333-555-2222','jdoe@email.net'
from dual;

unning yields the following:

Donor:John Q Public Amount: 100
Donor:Percival Lowell Amount: 200
Donor:Jane Doe Amount: 400

Changing Jane Doe’s donation amount to –400 causes the following to be output:

SQL Error:ORA-20001: Donations must be positive
ORA-06512:at "DEMO.DONATION_INS_TRG", line 17
ORA-04088:error during execution of trigger 'DEMO.DONATION_INS_TRG'

The after trigger did not fire.Returning the donation amount to 400 and executing again yields the following:

Donor:John Q Public Amount: 100
Donor:Percival Lowell Amount: 200
Donor:Jane Doe Amount: 400

If this were written as a simple trigger,there would have been five lines listed since the declaration of messQueue also initialized the variable.

Compound triggers behave a little differently when used with FORALL statements.The following shows the prior example written as a PL/SQL anonymous block that creates the records in the PL/SQL table and uses FORALL to bulk insert them:

declare
TYPE donTab_t IS TABLE OF donations%ROWTYPE;
SUBTYPE donRec_t IS donations%ROWTYPE;
donArr donTab_t:=donTab_t();
FUNCTION newDonRec_t(donation_id IN PLS_INTEGER,doner_name IN VARCHAR2
,donation_amount IN NUMBER,address_1 IN VARCHAR2
,address_2 IN VARCHAR2,address_3 IN VARCHAR2
,city IN VARCHAR2,state IN VARCHAR2
,postal_code IN VARCHAR2,country IN VARCHAR2
,phone IN VARCHAR2,email IN VARCHAR2)
RETURN donRec_t IS
tmp donRec_t;
BEGIN
tmp.donation_id:=donation_id;
tmp.donor_name:=doner_name;
tmp.donation_amount:=donation_amount;
tmp.address_1:=address_1;
tmp.address_2:=address_2;
tmp.address_3:=address_3;
tmp.city:=city;
tmp.state:=state;
tmp.postal_code:=postal_code;
tmp.country:=country;
tmp.phone:=phone;
tmp.email:=email;
return tmp;
END;
BEGIN
donArr.EXTEND;
donArr(donArr.LAST):=newDonRec_t(donations_s.nextval,'John Q Public',100,
'101 Main St.',null,null,'Anytown','ST','0000000','US',
'111-555-3333','jqpublic@email.net');
donArr.EXTEND;
donArr(donArr.LAST):=newDonRec_t(donations_s.nextval,'Percival Lowell',200,
'100 Cactus Ln',null,null,'Flagstaff','AZ','86001','US',
'928-555-0000','plowell@email.net');
donArr.EXTEND;
donArr(donArr.LAST):=newDonRec_t(donations_s.nextval,'Jane Doe',400,
'101 Elm',null,null,'Peaceville','ST','0000000','US',
'333-555-2222','jdoe@email.net');
-- insert the records
FORALL I IN 1..donArr.COUNT
INSERT INTO donations VALUES donArr(i);
END;
/

The function newDonRec_t() initializes a record of type donations%RECTYPE. When this block is executed,the same result is seen. However,when you change Jan Doe’s donation amount to –400 as before,the following is emitted:

CREATE OR REPLACE TRIGGER donation_ins_trg
FOR INSERT ON donations
COMPOUND TRIGGER
livePipe LIVE_FEED.pipe_t;
messQueue LIVE_FEED.messTab_t;
badCnt SIMPLE_INTEGER:=0;
BEFORE STATEMENT IS
BEGIN
livePipe:=LIVE_FEED.getPipe();
messQueue:=LIVE_FEED.messTab_t();
EXCEPTION
WHEN NO_DATA_FOUND THEN
livePipe:=NULL;
END BEFORE STATEMENT;
BEFORE EACH ROW IS
BEGIN
badCnt:=badCnt+1;
IF :new.donation_amount<=0 THEN
RAISE_APPLICATION_ERROR(-20001,'Donations must be positive');
END IF;
END BEFORE EACH ROW;
AFTER EACH ROW IS
BEGIN
badCnt:=badCnt-1;
IF livePipe IS NOT NULL THEN
messQueue.EXTEND;
messQueue(messQueue.LAST).donor_name:=:new.donor_name;
messQueue(messQueue.LAST).donation_amount:=:new.donation_amount;
END IF;
END AFTER EACH ROW;
AFTER STATEMENT IS
BEGIN
IF livePipe IS NOT NULL AND badCnt=0 THEN
LIVE_FEED.enqueue(livePipe,messQueue);
END IF;
END AFTER STATEMENT;
END donation_ins_trg;

The badCnt variable is incremented in the BEFORE EACH ROW section anddecremented in the AFTER EACH ROW section.If some error occurs,the AFTER EACH ROW section will not be called,and badCnt will retain a nonzero value.The AFTER STATEMENT trigger checks the value of badCnt and calls LIVE_FEED.enqueue() only if there’s a pipe and badCnt is zero.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Oracle 11g Topics