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.