A trigger is a block of Oracle PL/SQL code that executes in response to some predetermined event. Specifically, this event involves inserting, modifying, or deleting table data, and the task can occur either prior to or immediately following any such event. This three-part article explains triggers, one of Oracle’s key features that supplement what you cannot easily accomplish with Oracle’s built-in referential integrity features. It 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).
This chapter first introduces you to triggers, offering general examples that illustrate how you can use them to carry out tasks such as enforcing business rules and preventing invalid transactions. This chapter then discusses Oracle’s trigger implementation, showing you how to create, execute, and manage triggers. Finally, you’ll learn how to incorporate trigger features into your PHP-driven Web applications.
Introducing Triggers
As developers, we have to remember to implement an extraordinary number of details in order for an application to operate properly. Of course, much of the challenge has to do with managing data, which includes tasks such as the following:
Preventing corruption due to malformed data
Enforcing business rules by ensuring that an insert of an item from an e-commerce store into theORDER_ITEMtable automatically calculates an estimated delivery date and shipping cost and inserts those values into other columns of theORDERandORDER_ITEMrows
Automatically retrieving a unique number from an Oracle sequence and using it as the primary key of an inserted row
Capturing usage information not available from Oracle’s built-in auditing
Modifying rows in one or more base tables when a user performs DML operations against a view
If you’ve built even a simple application, you’ve likely spent some time writing code to carry out at least some of these tasks. Given the choice, you’d probably rather have some of these tasks carried out automatically on the server side, regardless of which application is interacting with the database. Database triggers give you that choice, which is why they are considered indispensable by many developers.
The utility of triggers stretches far beyond the aforementioned purposes. Suppose you want to update the corporate Web site when the $1 million monthly revenue target is met. Or suppose you want to e-mail any employee who misses more than two days of work in a week; or perhaps you want to notify a manufacturer if inventory runs low on a particular product. All of these tasks can be facilitated by triggers.
Many developers would argue that business logic is best suited for middleware applications. However, enforcing business logic at the database level using triggers makes more sense when the business rule must be enforced regardless of the application used to access the database. Using triggers may prevent ad hoc SQL statements from creating logical inconsistencies in the data when a developer or DBA bypasses the application that normally updates the database.
To provide you with a better idea of the utility of triggers, let’s consider two scenarios, the first involving a before trigger, or a trigger that occurs prior to an event, and the second involving an after trigger, or a trigger that occurs after an event. These two types of triggers conveniently correspond to Oracle Database XE’sBEFOREandAFTERtriggers.
