The Trigger-Happy DBA
November 26, 2003
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).