In this second part of a three-part series on Oracle triggers, you'll learn how to tell when to use a BEFORE trigger instead of an AFTER trigger, how to understand trigger events, and how to create a trigger. This article is excerpted from chapter 37 of the book Beginning PHP and Oracle: From Novice to Professional, written by W. Jason Gilmore and Bob Bryla (Apress; ISBN: 1590597702).
Before Triggers vs. After Triggers
You may be wondering how one arrives at the conclusion to use a BEFOREtrigger instead of anAFTERtrigger. For example, in theAFTERtrigger scenario in the previous section, why couldn’t the ticket reassignment take place prior to the change to the technician’s availability status? Standard practice dictates that you should use aBEFOREtrigger when validating or modifying data that you intend to insert or update. ABEFOREtrigger shouldn’t be used to enforce propagation or referential integrity because it’s possible that otherBEFOREtriggers could execute after it, meaning the executing trigger may be working with soon-to-be-invalid data. It’s also possible that anotherBEFOREtrigger will enforce another business rule that renders the transaction invalid.
On the other hand, anAFTER trigger should be used when data is to be propagated or verified against other tables and for carrying out calculations because you can be sure the trigger is working with the final version of the data.
Oracle’s Trigger Support
Because of Oracle Database XE’s rich support for built-in declarative integrity constraints, you may never need to create a trigger. In this section, we make sure you understand when triggers are not the best solution, saving you the time you would otherwise spend to write a trigger. In Chapter 30, we introduced foreign keys and how they can enforce referential integrity in your database. There is really no good reason to use a trigger if you can use a foreign key. A foreign key constraint check is more efficient than running a trigger because it’s built-in to the Oracle database engine. In addition, you don’t have to write even one line of PL/SQL code. Therefore, you should not use a trigger for integrity enforcement if you can use these built-in integrity constraints instead:
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
In the following sections, we tell you more about how Oracle implements triggers and some of the caveats when using triggers. Next, you’ll learn how to create, manage, and execute Oracle triggers using theTECHNICIANandTICKET tables presented earlier in the chapter.
