Q#4
Using triggers that call package procs
Oracle considered (many years ago) packages to be so fundamental that the package named
STANDARD
defines the PL/SQL environment – this in itself should be a convincing argument for using packages in larger-scale projects/apps.
From the Application Development Guide for Oracle 11.2:
“The STANDARD specification declares public types, variables, exceptions, subprograms, which are available automatically to PL/SQL programs. For example,STANDARDdeclares functionABS, which returns the absolute value of its argument, as follows:
FUNCTION ABS (n NUMBER) RETURN NUMBER;
The contents of packageSTANDARDare directly visible to applications. You need not qualify references to its contents by prefixing the package name. For example, you might invokeABSfrom a database trigger, stored subprogram, Oracle tool, or 3GL application, by:
abs_diff := ABS(x - y);
If you declare your own version ofABS, your local declaration overrides the public declaration. You can still invoke the SQL function by specifying its full name: abs_diff := STANDARD.ABS(x - y);”
A DB trigger is a special form of stored proc.
Just as an OS interrupt is intended to react immediately to and “handle” an interrupt-causing event, think of a DB trigger (hereafter referred to as s trigger) as a (PL/SQL) code body that immediately reacts to a pre-defined DB event. Examples of DB events are:
1) A user logs into the DB (a DB system-level event type) 2) a row in a user table is updated (a user-level event type), and so on.
A trigger is never explicitly “called” by user code, Just as with an OS interrupt, some defined DB event type happens, and that event automatically starts the execution of >=1 trigger in response.
As interesting and as “sophisticated” as triggers sound/seem, they should be used with great caution.
Highly-respected DB technologists warn against both a) mis-use and b) over-use of triggers. Most of the above applies equally to any modern RDBMS.
The shortcomings of triggers become obvious only when you are “on the job” – problems like the following will arise:
a) analyzing and tracing DB app code is more complicated – triggers are not visibly called, they just “happen”
b) triggers can have complicated cascading effects, as one trigger may cause a second trigger to fire, that may cause …
c) triggers confuse object dependency chains
This small demo question will, even if small, illustrates a “balanced” use of triggers.
By balanced is meant: like an OS interrupt handler, a trigger should not be tens/hundreds of lines-of-code. A trigger body should be short, to-the-point, and efficient. Whenever complex processing must be handled by a trigger, the complicated/dirty/low-level details code is best done by calling (one or more) packages.
Implement the following steps. You can use your own names for any of the items specified.
First, create table T with type number column c that is the PK.
Next, create a package specification named pq4 having public procedures named upd and del.
upd has two IN mode parameters of type number named old_value and new_value. del has one IN mode parameter named pk_value.
Next, implement the pq4 package body:
Trigger tr_tq4, defined below will call upd whenever specific kinds of row UPDATE statements are executed on T.
proc upd parameters refer to the passed previous value r.c of row r and updated value of r.c when row T.r is updated.
If new_value is 0 upd’s EXCEPTION section is entered and the following message is displayed:
USER EXCEPTION - UPDATE created c value 0; Containing transaction will be rolled back
and a “flag” variable of type varchar2(1) (rrefer to as do_rollback) is set to indicate that the tr containing this update will be rolled back.
Otherwise, when new_value is not 0, the difference (new_value – old_value) is added to a running total, total_upd_diffs, of such differences, and a 2-line and new message displaying the passed argument values and the new total_upd_diffs value is written:
BEFORE UPDATE:: old and new row values: -101 -202
New running total of change diffs is xxx
(xxx will now be 101 less than the running total before this row UODATE occurred)
Important Note on package variables – the difference values per row update are accumulated only for committed or uncommitted (at the time of the call to upd) update statements that fire tr_tq4 and did not cause an exception in the upd proc’s code.
Each package body has an optional “initialization block” following proc/fcn implementation code. It simply takes the form of an anonymous block that initializes package constants, variables, etc.
Use a simple initialization block to display message: “ Executing pkg pq4 Init block “ and to assign 0 to total_upd_diffs.
The proc del’s parameter pk_value refers to the PK value of a row that is being deleted from T. When called, del simply displays the following message about the PK xxx of the deleted row:
BEFORE DELETE:: Table tq4 row deleted having PK value xxx
Next, implement the trigger on the table
tr_tq4 is a BEFORE UDPDATE or DELETE type row level trigger on table T. tr_tq4 does NOT fire for every row UPDATE or DELETE on T. It files only for row UPDATE or DELETE for which the original r.c value is negative or greater than 1000.
Use a trigger WHEN clause to specify this trigger firing condition. (This illustrates a proper trigger definition in that the trigger body will fire for quite limited cases; it also implements an app condition that SQL CHECK constraints cannot handle)
The trigger body is simple thanks to the package procedures – tr_tq4 simply calls package proc upd when the row DML is UPDATE and calls package proc del when the row DML is DELETE.
Finally, execute the following statements in the order specified below.
Note and implement the comment that preceds the multi-statement transaction at the med of the commands list.
insert into tq4 values(1);
insert into tq4 values(2);
insert into tq4 values(3000);
insert into tq4 values(-101);
insert into tq4 values(6789);
commit;
update tq4
set c = c*2 where c = -101;
update tq4
set c = 4000 where c = 1;
delete from tq4 where c = 6789;
commit;
/*
The following tr (refer to as z) must be rolled back whenever one of its UPDATE statements in z
would set :new.c to 0
*/
begin
update tq4
set c = 25 where c = 3000;
update tq4
set c = 0 where c = -202;
IF pq4.do_rollback = 'Y' THEN
rollback;
END IF;
end;
/
show errors
/* Display final table contents */
select * from tq4;
/* Display the final value of package pq4 public variable total_upd_diffs */
begin
dbms_output.put_line('Final pq4.total_upd_diffs value is ' || pq4.total_upd_diffs);
end;