Who is the trigger-happy DBA? No, I do not mean the DBA who
is pulling his hair out over a developer’s mistake ("I truncated all my
tables. Why can’t you simply do a rollback for me?"), or the DBA fighting management’s
insistence on using export as the primary backup and recovery strategy ("Just
do one every hour if you’re so worried about losing data."). The
trigger-happy DBA I have in mind is the DBA who has a good understanding of the
use of triggers and what they can and cannot do for him.
Triggers can be an extremely useful tool to manage and
control data, and to enforce business rules and logic. Some may argue that this
usefulness can be managed just as well, if not better, by the use of other
functions such as procedures, constraints, and so on. And, no doubt, there are
some aspects of triggers that make using them unappealing. But on the whole,
triggers can be an effective tool in several areas. Some of the more commonly
used areas include auditing, enforcement of business rules, and behind the
scenes DML-related operations.
A quick review of what triggers are is in order. Oracle
defines triggers as "procedures that are stored in the database and
implicitly run, or fired, when something happens." [Page 15-1, Application
Developer’s Guide] What is the "something" that happens to make a
trigger fire? There are 12 "somethings" that can cause triggers to
fire: Before/After during Insert/Update/Delete on a Row/Table (which leads to
the 2x3x2=12 types). To be more precise in describing the number of triggers,
the 12 just shown are referred to as DML triggers, because insert, update and
delete are data manipulation operations.
The DML triggers are the best known, but there are more
available since Oracle8i was released. The additional triggers, or types of
triggers, are instead-of triggers, database event triggers, and DDL triggers.
Instead-of triggers are used when views are involved, and are a good way of
avoiding the mutating table trigger-related error. Database event triggers can
be divided into two categories: on the database, where triggers occur for all
users, and on a schema, where triggers occur for that user. Data definition
types of triggers can be used to capture events related to DDL statements such
as create, drop, and alter.
The remainder of this article will focus on DML triggers,
and later articles will cover the Oracle8i-era newer ones.
So what are some design issues when considering the use of
triggers? When considering which type of trigger to use in a DML operation, you
want to avoid having a hair trigger, that is, firing a trigger when it is not
the appropriate time to fire it. Conversely, you do not want the correct trigger
to fire late. Suppose you have a banking application with a trigger that checks
to see if the DML operation is occurring after business hours (assuming that it
should only be performed during business hours). The DML operation involves
updating a million records. It does not make sense to let Oracle perform the
update, only to have it canceled or rolled back because you used an AFTER
trigger instead of a BEFORE trigger to check the time of day. The general rule
here would be to check or enforce the business logic BEFORE using database
resources. In this example, an AFTER trigger can be used to record what
happened.
Using the banking example again (during business hours), say
you want to pay interest to customers with a balance over a certain amount. Which
would be appropriate, given that your user is trying to update data: a
row-level or table-level (also referred to as statement-level) trigger?
Suppose you said "table-level," because you are trying to update a
table. The DML statement even suggests "table" as the answer because
of how the DML statement is written:
SQL> update table_name set ...
Well, you’d be wrong! Only row-level triggers have access to
data in the rows. Anything that affects the actual data within a row requires a
row-level trigger. A statement-level trigger would be appropriate to record,
for example, the fact that the update statement completed, what time it
completed, or the name of the user who performed the operation.
So far, the banking example has only considered an update.
Suppose you wanted to use triggers for insert and delete DML statements. Do you
have to write separate triggers for each type of DML statement? You could, but
you do not have to. Your trigger code could have a structure like the one shown
below.
CREATE OR REPLACE TRIGGER your_trigger_name
BEFORE INSERT OR UPDATE OR DELETE ON YOUR_TABLE_NAME
FOR EACH ROW
BEGIN
IF INSERTING THEN…
–do whatever
END IF;
IF UPDATING THEN …
–do something else
END IF;
IF DELETING THEN …
–do something else
END IF;
END;
So far, we have seen what we can do with triggers. That begs
the question of "Are there any restrictions on using triggers?" Of
course there are! This is Oracle, remember? There are always some strings
attached to functionality. For the most part, the restrictions are pretty
minor, and some are transparent to most people.
Oracle places a size limit of 32KB on a trigger statement.
How "big" is 32KB of data? Up to the question mark, this article
used about 25KB, using around 870 words and over 4,000 characters, just to give
you a rough idea of how much code you can write under 32KB. Is there a way
around the 32KB limit? Yes, you can call external procedures.
If you do not use LONG or LONG RAW data types, any
restrictions concerning these are transparent to you. Several other
restrictions are listed in the Oracle9i Application Developer’s Guide. One
interesting restriction has to do with the order in which triggers are fired.
The code snippet shown earlier contained each type of DML
statement, and if you are using more than one than one type of trigger, Oracle
fires all triggers of a type, then all triggers of another type, and so on. You
have no control over which type is fired first, and whichever type is fired
after the first type will see any changes made by the first type, and that
cascades down (third type sees changes made by the second type, and so on).
Finally, the mutating table error, ORA-04091 table
owner.table_name is mutating, trigger/function may
not see it has made DBAs
trigger-happy in ways we would rather they not be exposed to. Next month’s
article starts with the use of instead-of triggers, which are quite handy in
getting around the mutating table error (which will also be explained in more
detail).
