 |
|
Administration of Triggers in
Oracle9i
Oracle Tips by Burleson Consulting
|
Database triggers are PL/SQL procedures stored
in the database and associated with specific actions on a database
level. Beginning with Oracle8, a new type of trigger called an INSTEAD
OF trigger could be created exclusively for views. Under Oracle8i and
Oracle9i, the concept of database event triggers has been added. A
database event trigger triggers on DDL or database events such as
STARTUP, SHUTDOWN, LOGIN, LOGOFF, or server errors.
Tip: Never depend on the order in which
triggers fire. This is not guaranteed to always be identical.
Creation of Database Triggers
Database triggers are created using the CREATE
TRIGGER command, and are ENABLED upon creation. There were three basic
types of triggers in Oracle7: BEFORE, AFTER, and FOR EACH ROW. Under
Oracle8, a fourth was added: the INSTEAD OF trigger for views. Since
the FOR EACH ROW clause can be combined with the other two non-view
triggers, this gives four types of triggers: BEFORE and AFTER
statement triggers and BEFORE and AFTER row triggers. In addition,
each of the four types can be tied to the three basic actions, DELETE,
INSERT, and UPDATE, resulting in 12 possible triggers per table.
A view can only have an INSTEAD OF trigger
(under Oracle8 only). The Oracle8i DDL and database event triggers are
also created using the CREATE TRIGGER command. DDL events that can
cause a trigger to fire are CREATE, ALTER, or DROP on clusters,
tables, views, procedures, functions, packages, roles, sequences,
synonyms, indexes, tablespaces, triggers, types, and users. The
database event triggers are LOGON, LOGOFF, SERVERERROR, STARTUP, and
SHUTDOWN, with the LOGON, STARTUP, and SERVERERROR coming AFTER event
triggers. The LOGOFF and SHUTDOWN are BEFORE event triggers. You can
find the exact syntax for the CREATE TRIGGER command in the SQL
reference at the techent.oracle.com Web site.
Database triggers are complex, so if you do
not save the creation script, it will be very difficult to readily
recall the exact command used in many cases. The script in
TRIG_RCT.SQL, available from the Wiley Web site, can be used to
retrieve trigger definitions for the database.
Prior to the release of Oracle version 7.3,
triggers were compiled at runtime. Since 7.3, they are stored in the
database as Pcode. This provides significant performance benefits over
earlier versions since the overhead of reparsing the trigger for each
firing is eliminated. This allows larger and more complex triggers to
be created without fear of performance degradation caused by reparsing
large sections of code. In versions 8, 8i and 9i triggers can be
pinned into memory using the DBMS_SHARED_POOL.KEEP(‘trigger_name’,
‘T’) procedure call.
Conditional Predicates
New to Oracle8 and continued in Oracle8i and
Oracle9i is the concept of a conditional predicate for a trigger that
tells the trigger why it is being fired.
INSERTING. Evaluates to TRUE if an
insert operation fired the trigger.
DELETING. Evaluates to TRUE if a delete
operation fired the trigger.
UPDATING. Evaluates to TRUE if an
update operation fired the trigger.
UPDATING(column). Evaluates to TRUE if
the operation is an update and the specified column is updated.
LOB Trigger Limitations
You can reference object, varray, nested
table, LOB, and REF columns inside triggers, but you cannot modify
their values. Triggers based on actions against these types of
attributes can be created.
Triggers and Mutating Tables
One recurring problem with triggers is when
one tries to reference the table for which it is fired (for example,
to check a date range). This self-referencing results in “ORA-04091
table ‘table_name’ is mutating, trigger/ function may not see it.”
Usually, this situation can be remedied in one of three ways: by using
a temporary PL/SQL table to hold values, by using a temporary table to
hold values, or by using a view. The key here is to remove the values
to be selected against from the table before you attempt the
operation, and then refer to this remote source for any value
checking. It might also be possible to create an index that any
selects will reference. Any solution that moves the data from the
table to a secondary source that can then be used in place of the
table itself should correct this problem.
Let’s look at an example of a trigger that is
set to fire on an insert or update to a purchase_orders table:
CREATE OR
REPLACE TRIGGER purchase_order_adjust
BEFORE INSERT OR UPDATE ON purchase_orders
FOR EACH ROW
WHEN (new.price>0)
DECLARE
adj_price NUMBER;
RATE number;
BEGIN
SELECT adjust_rate
INTO rate
FROM price_adjust
WHERE state_code = :new.state_code;
adj_price:=:new.price*rate;
:new.adjusted_price:=adj_price;
END;
Here is the result:
SQL>
update purchase_orders set price=2000 where po_num=1
2
and lineno=1;
1 row updated.
SQL> select
* from purchase_orders;
LINENO
PRICE ADJUSTED_PRICE PO_NUM ST
------- ------- -------------- -----------
1 2000
2130 1 GA
For both inserts and updates, the adjusted
price column is set to the price, times the rate from the price_adjust
table, based on the state. See how all of this is hidden from the
user? Even at the base SQL level, the user has no indication that any
of the trigger activity is occurring. This is a simple form of data
hiding. Because the trigger is stored as being associated only with a
single table, it is also a simple form of encapsulation (except that
they aren’t stored together as one entity).
Use of Conditional Predicates
Conditional predicates allow a single trigger
to decide which action to take based on the triggering action. For
example, different actions could be taken if a table were inserted
into, updated, or deleted from. The action could be as simple as
inserting a row into an auditing table for each table action or
updating multiple tables with the results of a changed calculation.
For our example, we have a purchase_orders
table with line number, price, adjusted price, purchase order number,
and state code; a price_adjust table with state_code and adjustment
rate; and a billings table with a total price, purchase order number,
and purchaser name.
The trigger we want to build updates the total
price column of the billings table each time a change is made to a
purchase order number line item in the purchase_orders table. I know
this wastes resources and calculated values shouldn’t be stored in a
database, but this is just an example.
Here is the trigger we want to create:
CREATE OR
REPLACE TRIGGER maintain_total
BEFORE INSERT OR DELETE OR UPDATE
ON purchase_orders FOR EACH ROW
DECLARE
new_total NUMBER;
chg_date DATE;
BEGIN
SELECT SUM(adjusted_price), sysdate
INTO new_total,chg_date
FROM purchase_orders
WHERE po_num=:new.po_num;
IF INSERTING THEN
UPDATE billings
SET po_amount=new_total,
change_date=chg_date,
change_type='INSERT'
WHERE po_num=:new.po_num;
ELSIF DELETING THEN
UPDATE billings
SET po_amount=new_total,
change_date=chg_date,
change_type='DELETE'
WHERE po_num=:new.po_num;
ELSIF UPDATING THEN
UPDATE billings
SET po_amount=new_total,
change_date=chg_date,
change_type='UPDATE'
WHERE po_num=:new.po_num;
END IF;
END;
The code compiles fine. Let’s run it:
SQL> update
purchase_orders set price=3000 where po_num=1;
update purchase_orders set price=3000 where po_num=1
*
ERROR at line 1:
ORA-04091: table TELE_Remote DBA.PURCHASE_ORDERS is mutating,
trigger/function may not see it
ORA-06512: at "TELE_Remote DBA.MAINTAIN_TOTAL", line 5
ORA-04088: error during execution of trigger 'TELE_Remote DBA.MAINTAIN_TOTAL'
What happened? Remember the mutating table
errors? This is a classic way of generating them. Essentially, we have
a value that we are updating, so it is in a state of flux (our first
trigger from the previous guide is firing at the same time this one
fires, leaving a value in a transient state; hence, the mutation).
How can we fix this? We must change the
trigger to an AFTER type. This means eliminating all OLD and NEW
references and using logic closer to that of a stored PROCEDURE using
cursors. In pre-7.3 days, we would have done this anyway because
PROCEDUREs were compiled and triggers were not. Here is the new
trigger code:
CREATE OR
REPLACE TRIGGER maintain_total
AFTER INSERT OR DELETE OR UPDATE
ON purchase_orders
DECLARE
adj_price NUMBER;
RATE number;
new_total NUMBER;
chg_date DATE;
po NUMBER;
CURSOR get_po IS
SELECT UNIQUE po_num
FROM billings;
CURSOR get_total(po number) IS
SELECT SUM(adjusted_price), sysdate
FROM purchase_orders
WHERE po_num=po;
BEGIN
OPEN get_po;
FETCH get_po into po;
LOOP
EXIT WHEN get_po%NOTFOUND;
OPEN get_total(po);
FETCH get_total INTO new_total,chg_date;
IF INSERTING THEN
UPDATE billings
SET po_amount=new_total,
change_date=chg_date,
change_type='INSERT'
WHERE po_num=po and
po_amount<>new_total;
ELSIF DELETING THEN
UPDATE billings
SET po_amount=new_total,
change_date=chg_date,
change_type='DELETE'
WHERE po_num=po and
po_amount<>new_total;
ELSIF UPDATING THEN
UPDATE billings
SET po_amount=new_total,
change_date=chg_date,
change_type='UPDATE'
WHERE po_num=po and
po_amount<>new_total;
END IF;
CLOSE get_total;
FETCH get_po INTO po;
END LOOP;
END;
So what happens now when we fire off an update
into the table?
SQL> select
* from billings;
PO_NUM
PO_AMOUNT PURCHASER
CHANGE_DA CHANGE_TYP
---- -----
------------------
----------- ---------
1 3195
Georges Shootery
24-JUL-97 Initial
SQL> update
purchase_orders set price=2000 where po_num=1;
1 row
updated.
SQL> select
* from billings;
PO_NUM
PO_AMOUNT PURCHASER
CHANGE_DA CHANGE_TYP
---- -----
------------------
------------ --------
1 2130
Georges Shootery
24-JUL-97 UPDATE
Our trigger works (and, yes, I did check it
out on all three actions: insert, update and delete). What did we
change? We made the trigger an AFTER action trigger instead of a
BEFORE action. This changed the timing to eliminate the conflict with
the other trigger. Because we made the trigger an AFTER action
trigger, the capability of using the NEW and OLD qualifiers
disappeared, so now we have to use cursor logic instead.
Using cursors, we have to loop through the
entire set of purchase orders—not very efficient, but it works. We
assumed that a change would affect a total amount. If a change doesn’t
alter the total, then no processing is done for the purchase order.
Let’s look at a simple example of a database
trigger. Here we want to capture who has logged in and the date they
logged in (yes, I realize the audit trail will allow us to do this,
but this is just for demonstration purposes). The trigger would
require a table to capture the data:
CREATE TABLE
GET_LOGINS (login_rec VARCHAR2(60), login_date DATE);
CREATE OR REPLACE TRIGGER capture_logins AFTER LOGON ON DATABASE
DECLARE text
VARCHAR2(30);
BEGIN
SELECT user||' logged into '||value
INTO text
FROM v$parameter
WHERE name = 'db_name';
INSERT INTO get_logins VALUES (text,sysdate);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
The above trigger will now fire on each logon
to the database and capture all users and their login times. Note the
use of the EXCEPTION clause. Especially for LOGON triggers, this is
critical since any error in the trigger logic that generates an
exception will prevent all users from logging on the system. I had to
log in using the SVRMGRL command as INTERNAL and drop the trigger when
I misspelled ‘db_name’ as ‘db name’, causing a “no rows found”
exception.
Let’s look at an example of a DDL
database-level trigger before we move on:
CREATE OR
REPLACE TRIGGER test_ddl_trig AFTER DROP ON SCHEMA
DECLARE
text VARCHAR2(255);
BEGIN
SELECT user||' dropped an object'
INTO text FROM dual;
INSERT INTO test_trig VALUES (text,sysdate);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
As a result of this trigger, any drop of any
schema object by anyone is monitored.
See
Code Depot for Full Scripts
 |
This is an excerpt
from Mike Ault, bestselling author of "Oracle
10g Grid and Real Application Clusters".
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
 |
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today. |
 |
|