Beginning PHP and Oracle From Novice to Professional by W. Jason Gilmore and Bob Bryla Chapter 37

Atrigger 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 chapter introduces triggers, one of Oracle’s key features that supplement what you cannot easily accomplish with Oracle’s built-in referential integrity features.
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 the ORDER_ITEM table automatically calculates an estimated delivery date and shipping cost and inserts those values into other columns of the ORDER and ORDER_ITEM rows
• 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 facil- itated by triggers.

649

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 trig- gers 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’s BEFORE and AFTER triggers.

Taking Action Before an Event

Suppose that a gourmet-food distributor gives automatic 20 percent discounts for an order line item if the customer orders premium coffee and it’s Monday. The pseudocode for this discounting process looks like this:

Shopping cart insertion request submitted: Set item_discount_amount = 0;
If product_id = "coffee" and day = "Monday":
Set item_discount_amount = item_amount * 0.20; End If
Process insertion request

Taking Action After an Event

Most help desk support software is based upon the paradigm of ticket assignment and resolution. Tickets are both assigned to and resolved by help desk technicians, who are responsible for logging ticket information. However, occasionally even the technicians are allowed out of their cubicles, sometimes even for a brief vacation or because they are ill. Clients can’t be expected to wait for a technician to return, so the technician’s tickets should be placed back in the pool for reassignment by the operations manager. This process should be automatic so that outstanding tickets aren’t potentially ignored. Therefore, it makes sense to use a trigger to ensure that the matter is never over- looked.
For the purposes of this example, assume that the TECHNICIAN table looks like the table in
Figure 37-1, viewed from the Object Browser in the Oracle Database XE Web interface.

Figure 37-1. The TECHNICIAN table

The TICKET table looks like the table in Figure 37-2.

Figure 37-2. The TICKET table

Therefore, to designate a technician as out-of-office, the AVAILABLE flag needs to be set accord- ingly (0 for out-of-office, 1 for in-office) in the TECHNICIAN table. If a query is executed setting that column to 0 for a given technician, his or her tickets should all be placed back in the general pool for eventual reassignment. The AFTER trigger pseudocode looks like this:

Technician table update request submitted: If available column set to 0:
Update helpdesk ticket table, setting any flag assigned to the technician back to the general pool.
End If

Later in this chapter in the section “Leveraging Triggers in PHP Applications,” you’ll learn how to implement this trigger and incorporate it into a Web application.

Before Triggers vs. After Triggers

You may be wondering how one arrives at the conclusion to use a BEFORE trigger instead of an AFTER trigger. For example, in the AFTER trigger 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 a BEFORE trigger when validating or modifying data that you intend to insert or update. A BEFORE trigger shouldn’t be used to enforce propagation or referential integrity because it’s possible that other BEFORE triggers could execute after it, meaning the executing trigger may be working with soon-to-be-invalid data. It’s also possible that another BEFORE trigger will enforce another business rule that renders the transaction invalid.
On the other hand, an AFTER 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 the TECHNICIAN and TICKET tables presented earlier in the chapter.

Understanding Trigger Events

Several types of events fire a trigger:

• DML statements: INSERT, UPDATE, or DELETE on a table or view

• DDL statements: CREATE or ALTER statements issued by a specific user or by any user in the database
• System events: Database startup, shutdown, and errors

• User events: User logon or logoff

In the next section, we give you an example of creating a DML statement trigger. This trigger fires when the availability of a technician changes by changing the AVAILABLE flag to 0. DDL triggers, system event triggers, and user event triggers are beyond the scope of this book.

Creating a Trigger

Oracle triggers are created using a rather straightforward SQL syntax, similar to that used to create PL/SQL procedures. This is not surprising, since the triggered actions have a syntax virtually iden- tical to that of a stored procedure. The command-line syntax prototype follows:

CREATE TRIGGER <trigger name>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE } ON <table name>
FOR EACH ROW
[WHEN (<restriction clause>)]
<triggered PL/SQL block>

TRIGGER NAMING CONVENTIONS

Although not a requirement, it’s a good idea to devise some sort of naming convention for your triggers so that you can more quickly determine the purpose of each. For example, you might consider prefixing each trigger title with one of the following strings, as shown in the example in Figure 37-4:

• AD: Execute trigger after a DELETE statement has been executed.

• AI: Execute trigger after an INSERT statement has been executed.

• AU: Execute trigger after an UPDATE statement has been executed.

• BD: Execute trigger before a DELETE statement has been executed.

• BI: Execute trigger before an INSERT statement has been executed.

• BU: Execute trigger before an UPDATE statement has been executed.

As you can see from the prototype, it’s possible to specify whether a trigger should execute before or after the query, whether it should take place on row insertion, modification, or deletion, and to what table the trigger applies. In addition, you can restrict the trigger to run on rows that fulfill the condition in the WHEN clause; you will specify the contents of the WHEN clause using the GUI in the example that follows.
The GUI-based Oracle Database XE interface makes it easy to create and view triggers. Even
though you can use a command-line interface using the previous prototype, we use the GUI to step through the trigger creation process. From the GUI home page, click the Object Browser icon. By default, you will see a list of tables owned by the user logged into the database. Click the Create button, and click the Trigger link. You will see the Trigger dialog shown in Figure 37-3.
Enter the name of the target table, that is, the table whose rows will fire the trigger when rows
are deleted, updated, or inserted. In this example, you enter TECHNICIAN or select TECHNICIAN using the drop-down if the table is owned by the current schema user. Click the Next button to continue.

Figure 37-3. Specifying the target table name in the trigger creation dialog

Next you fill in the following details for the trigger, as shown in Figure 37-4:

• Name of the trigger

• When the trigger fires

• What kind of DML causes the trigger to fire

• Whether the trigger fires for each affected row or only once

• An optional WHERE clause

• The trigger body (PL/SQL block to implement the trigger logic)

In this example, you modify the default trigger name by adding the prefix AU per the naming convention. The trigger will fire after updates to the TECHNICIAN table, and only when the technician table is updated. Select For Each Row since you want to update tickets for each technician who is not available. More than one technician can be updated in an UPDATE statement on the TECHNICIAN table.
The string in the When text box, shown in Figure 37-4, inserted into a WHEN clause in the trigger by the Create Trigger GUI application, is as follows:

NEW.AVAILABLE = 0

Figure 37-4. Specifying trigger options and logic

The NEW qualifier indicates that you’re checking the new (updated) value of the AVAILABLE flag. If the new value is 0, the technician is temporarily not available and you need to execute the body of this trigger to release his or her tickets to other technicians. The trigger body is very simple. Change all tickets for the unavailable technician to 0 so that another technician can be assigned to this ticket:
UPDATE TICKET
SET TECHNICIAN_ID = 0
WHERE TECHNICIAN_ID = :NEW.TECHNICIAN_ID;

The :NEW qualifier in the WHERE clause is similar to the NEW qualifier in the WHEN clause; it specifies that you want to use the new value of the technician ID number when performing the update. In some cases you could use :OLD.TECHNICIAN_ID with the same results, except in the situation where an update to a technician changes both the technician ID and the availability code in the same trig- gering UPDATE. Therefore, you use :NEW.
Click Next and you see the confirmation screen in Figure 37-5. If you click the SQL link, you can see the CREATE TRIGGER command that will be executed. Click Finish to create the trigger and return to the page with the new trigger’s details.

Figure 37-5. Confirm trigger creation request

For each row affected by an update to the TECHNICIAN table, the trigger will update the TICKET table, setting TICKET.TECHNICIAN_ID to 0 wherever the TECHNICIAN_ID value specified in the UPDATE query exists. You know the query value is being used because the alias :NEW prefixes the column name. It’s also possible to use a column’s original value by prefixing it with the :OLD alias.
Once the trigger has been created, go ahead and test it by inserting a few rows into the TICKET
table and executing an UPDATE query that sets a technician’s AVAILABILITY column to 0:

update technician set available=0 where technician_id=4;

Now check the TICKET table, and you’ll see that the ticket assigned to Kelly (in Figures 37-1 and
37-2) is no longer assigned to her.

Viewing Existing Triggers

Using the Oracle Database XE GUI, it’s easy to view existing triggers. From the Object Browser page, select Triggers. In the scroll box on the left, select the trigger to view. In Figure 37-6, the trigger AU_TECHNICIAN_T1 is selected and you can see the details of the trigger itself.

Figure 37-6. Trigger details

Clicking the SQL tab above the trigger details shows you the SQL used to create the trigger:

CREATE OR REPLACE TRIGGER "AU_TECHNICIAN_T1" AFTER
update on "TECHNICIAN"
for each row
WHEN (NEW.AVAILABLE = 0) begin
UPDATE TICKET
SET TECHNICIAN_ID = 0
WHERE TECHNICIAN_ID = :NEW.TECHNICIAN_ID;
end;
/
ALTER TRIGGER "AU_TECHNICIAN_T1" ENABLE
/

Modifying or Deleting a Trigger

There is no functionality for modifying an existing trigger using the Oracle Database XE GUI. There- fore, you must copy the attributes of the existing trigger, drop it using the Drop button shown in Figure 37-6, and recreate it using the steps outlined previously.

If you do not have the Oracle Database XE GUI available, you can drop the trigger using the SQL Commands interface or another SQL command-line interface using the DROP TRIGGER command
as follows:

DROP TRIGGER AU_TECHNICIAN_T1;

■Caution When you drop a table, all triggers defined against the table are also deleted.

Leveraging Triggers in PHP Applications

Because triggers occur transparently, you really don’t need to do anything special to integrate their operation into your Web applications. Nonetheless, it is worth offering an example demonstrating just how useful this feature can be in terms of both decreasing the amount of PHP code and further simplifying the application logic. Therefore, in this section you’ll learn how to implement the help desk application described earlier in this chapter.
To begin, create the two tables TECHNICIAN and TICKET with a few rows in each, as shown earlier in Figures 37-1 and 37-2. Next, create the trigger AU_TECHNICIAN_T1, shown earlier in Figure 37-4.
Recapping the scenario, submitted help desk tickets are resolved by assigning each to a techni- cian. If a technician is out of the office for an extended period of time, say due to a vacation or an illness, they are expected to update their profile by changing their availability status. The profile manager interface looks similar to that shown in Figure 37-7, using the PHP code later in this section.

Figure 37-7. The profile manager interface

When the technician makes any changes to this interface and submits the form, the code presented in Listing 37-1 is activated.

Listing 37-1. Updating the Technician Profile (upd_tech_prof.php)

<form action="<?php echo $_SERVER['PHP_SELF'];?>" method="post">
<p><b>Update your profile.</b></p>
<p>
Technician ID:<br />
<input type="text" name="technician_id" size="6" maxlength="6" value="" />
</p>
<p>
Name:<br />
<input type="text" name="name" size="25" maxlength="25" value="" />
</p>
<p>
EMail Address:<br />
<input type="text" name="email" size="40" maxlength="40" value="" />
</p>
<p>
Availability (0=unavailable, 1=available):<br />
<input type="text" name="available" size="1" maxlength="1" value="1" />
</p>
<p>
<input type="submit" name="submit" value="Update!" />
</p>
</form>

<?php
if (isset($_POST['submit']))
{
// Connect to Oracle Database XE
$c = oci_connect('hr', 'hr', '//localhost/xe');

// Assign the POSTed values for convenience

$technician_id = $_POST['technician_id'];
$name = $_POST['name'];
$email = $_POST['email'];
$available = $_POST['available'];

// Create and run the UPDATE statement
$result = oci_parse($c,
"UPDATE technician SET name='$name', email='$email', available='$available' WHERE technician_id='$technician_id'");
oci_execute($result);

echo "<p>Thank you for updating your profile.</p>";

if ($available == 0) {
echo "<p>Because you'll be out of the office, your tickets will be reassigned to another technician.</p>";
}
oci_close($c);
}
?>

Once you execute this code via the included form and set the status to unavailable for a given technician, query the TICKET table and you will see that the relevant tickets have been unassigned. Note that there are no references to the trigger within the PHP code itself; it happens transparently behind the scenes in Oracle Database XE.

Summary

This chapter introduced triggers, a feature that can help you automate database integrity and the enforcement of complex business rules that otherwise would have to be enforced in the application (although many flame wars have erupted over the best place to implement business logic). Triggers can greatly reduce the amount of code you need to write solely for ensuring the referential integrity and business rules of your database. You learned about the different trigger types and the conditions under which they will execute. We offered an introduction to Oracle Database XE’s trigger imple- mentation, followed by coverage of how to integrate these triggers into your PHP applications.
In the next chapter we’ll shift gears a bit and focus on database performance, using several different types of Oracle indexes, and when to use them to optimally retrieve table rows.