CHAPTER 33
MySQL Triggers
A trigger is a task 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, a feature available as of MySQL 5.0.2. This chapter begins by offering general examples that illustrate how you can use triggers to carry out tasks such as enforcing referential integrity and business rules, gathering statistics, and preventing invalid transactions. This chapter then discusses MySQL'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, such as ensuring that an attempt to insert information about a product into the
producttable includes the identifier of a manufacturer whose information already resides in themanufacturertable - Ensuring database integrity by cascading changes throughout a database, such as removing all products whose manufacturer ID matches one you'd like to remove from the system
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.
Why Use Triggers?
You might consider using triggers for any of the following purposes:
- Audit trails: Suppose you are using MySQL to log Apache traffic (say, using the Apache
mod_log_sqlmodule) but you also want to create an additional special logging table that tracks just site zone traffic and enables you to quickly tabulate and display the results to an impatient executive. Executing this additional insertion can be done automatically with a trigger. - Validation: You can use triggers to validate data before updating the database, such as to ensure that a minimum-order threshold has been met.
- Referential integrity enforcement: Sound database administration practice dictates that table relationships remain stable throughout the lifetime of a project. Rather than attempt to incorporate all integrity constraints programmatically, it occasionally may make sense to use triggers to ensure that these tasks occur automatically.
The utility of triggers stretches far beyond these 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 handled by triggers.
To provide you with a better idea of the utility of triggers, let's consider two scenarios, the first involving a before trigger, a trigger that occurs prior to an event, and the second involving an after trigger, a trigger that occurs after an event.
Taking Action Before an Event
Suppose that a gourmet-food distributor requires that at least $10 of coffee be purchased before it will process the transaction. If a user attempts to add less than this amount to the shopping cart, that value will automatically be rounded up to $10. This process is easily accomplished with a before trigger, which, in this example, evaluates any attempt to insert a product into a shopping cart, and increases any unacceptably low coffee purchase sum to $10. The general process looks like this:
Shopping cart insertion request submitted:
If product identifier set to "coffee":
If dollar amount < $10:
Set dollar amount = $10;
End If
End If
Process insertion request
Taking Action After an Event
Most helpdesk support software is based upon the notion of ticket assignment and resolution. Tickets are both assigned to and resolved by helpdesk technicians, who are responsible for logging ticket information. However, occasionally even the technicians are allowed out of their cubicle, sometimes even for a brief vacation or because they are ill. Clients can't be expected to wait for the technician to return during such absences, so the technician's tickets should be placed back in the pool for reassignment by the 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 overlooked.
For purposes of example, assume that the technicians table looks like this:
+--------+---------+--------------------------+------------+
| id | name | email | available |
+--------+---------+--------------------------+------------+
| 1 | Jason | [email protected] | 1 |
| 2 | Robert | [email protected] | 1 |
| 3 | Matt | [email protected] | 1 |
+--------+---------+--------------------------+------------+
The tickets table looks like this:
+------+-----------+-----------------+---------------------+----------------+
| id | username | title | description | technician_id |
+------+-----------+-----------------+---------------------+----------------+
| 1 | smith22 | disk drive | Disk stuck in drive | 1 |
| 2 | gilroy4 | broken keyboard | Enter key is stuck | 1 |
| 3 | cornell15 | login problems | Forgot password | 3 |
| 4 | mills443 | login problems | forgot username | 2 |
+------+-----------+-----------------+---------------------+----------------+
Therefore, to designate a technician as out-of-office, the available flag needs to be set accordingly (0 for out-of-office, 1 for in-office) in the technicians table. If a query is executed setting that column to 0 for a given technician, his tickets should all be placed back in the general pool for eventual reassignment. The after trigger process looks like this:
Technician table update request submitted:
If available column set to 0:
Update tickets table, setting any flag assigned
to the technician back to the general pool.
End If
Later in this chapter, 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 in lieu 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.
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.
In the following sections, you'll learn how to create, manage, and execute MySQL triggers most effectively. Numerous examples involving trigger usage in PHP/MySQL-driven applications are also presented.
MySQL's Trigger Support
MySQL supports triggers as of version 5.0.2, but at the time of writing, this new feature was still under heavy development. While the previous introductory examples demonstrate what's already possible, there are still several limitations. For instance, as of version 5.1.21 beta, the following deficiencies exist:
TEMPORARYtables are not supported: A trigger can't be used in conjunction with aTEMPORARYtable.- Views are not supported: A trigger can't be used in conjunction with a view.
- Result sets can't be returned from a trigger: It's only possible to execute INSERT, UPDATE, and DELETE queries within a trigger. You can also execute stored routines within a trigger, provided they don't return result sets, as well as the
SETcommand. - Transactions are not supported: A trigger can't be involved in the beginning or conclusion of a transaction (namely,
START TRANSACTION,COMMIT, andROLLBACKstatements cannot be used within a transaction). - Triggers must be unique: It's not possible to create multiple triggers sharing the same table, event (
INSERT,UPDATE,DELETE), and cue (before, after). However, because multiple commands can be executed within the boundaries of a single query (as you'll soon learn), this shouldn't really present a problem. - Error handling and reporting support is immature: Although, as expected, MySQL will prevent an operation from being performed if a before or after trigger fails, there is presently no graceful way to cause the trigger to fail and return useful information to the user.
While such limitations may leave you scratching your head regarding the practicality of using triggers at this stage, keep in mind that this is very much a work in progress. That said, even at this early developmental stage, there are several possibilities for taking advantage of this important new feature. Read on to learn how you can begin incorporating triggers into your MySQL databases, beginning with an introduction to their creation.
Creating a Trigger
MySQL triggers are created using a rather straightforward SQL statement. The syntax prototype follows:
CREATE
[DEFINER = { USER | CURRENT_USER }]
TRIGGER <trigger name>
{ BEFORE | AFTER }
{ INSERT | UPDATE | DELETE }
ON <table name>
FOR EACH ROW
<triggered SQL statement>
As you can see, it's possible to specify whether the 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.
The DEFINER clause determines which user account will be consulted to determine whether appropriate privileges are available to execute the queries defined within the trigger. If defined, you'll need to specify both the username and hostname using 'user@host' syntax (for example, 'jason@localhost'). If CURRENT_USER is used (the default), then the privileges of whichever account has caused the trigger to execute will be consulted. Only users having the SUPER privilege are able to assign DEFINER to another user.
Tip If you're using a version of MySQL earlier than 5.1.6, you need the SUPER privilege to create triggers; starting with 5.1.6, you can do so if your account is assigned the TRIGGER privilege.
The following implements the helpdesk trigger first described earlier in this chapter:
DELIMITER //
CREATE TRIGGER au_reassign_ticket
AFTER UPDATE ON technicians
FOR EACH ROW
BEGIN
IF NEW.available = 0 THEN
UPDATE tickets SET technician_id=0 WHERE technician_id=NEW.id;
END IF;
END;//
Note You may be wondering about the au prefix in the trigger title. See the sidebar "Trigger Naming Conventions" for more information about this and similar prefixes.
For each row affected by an update to the technicians table, the trigger will update the tickets table, setting tickets.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 tickets table and executing an UPDATE query that sets a technician's availability column to 0:
UPDATE technicians SET available=0 WHERE id =1;
Now check the tickets table, and you'll see that both tickets that were assigned to Jason are assigned no longer.
Viewing Existing Triggers
As of MySQL version 5.0.10, it's possible to view existing triggers in one of two ways: by using the SHOW TRIGGERS command or by using the information schema. Both solutions are introduced in this section.
The SHOW TRIGGERS Command
The SHOW TRIGGERS command produces several attributes for a trigger or set of triggers. Its prototype follows:
SHOW TRIGGERS [FROM db_name] [LIKE expr]
Because the output has a tendency to spill over to the next row, making it difficult to read, it's useful to execute SHOW TRIGGERS with the G flag, like so:
mysql>SHOW TRIGGERSG
Assuming only the previously created au_reassign_ticket trigger exists in the present database, the output will look like this:
*************************** 1. row ***************************
Trigger: au_reassign_ticket
Event: UPDATE
Table: technicians
Statement: begin
if NEW.available = 0 THEN
UPDATE tickets SET technician_id=0 WHERE technician_id=NEW.id;
END IF;
END
Timing: AFTER
Created: NULL
sql_mode: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
1 row in set (0.00 sec)
As you can see, all of the necessary descriptors can be found. However, viewing trigger information using the INFORMATION_SCHEMA database offers a vastly improved methodology. This solution is introduced next.
The INFORMATION_SCHEMA
Executing a SELECT query against the TRIGGERS table found in the INFORMATION_SCHEMA database displays information about triggers. This database was first introduced in Chapter 28.
mysql>SELECT * FROM INFORMATION_SCHEMA.triggers
->WHERE trigger_name="au_reassign_ticket"G
Executing this query retrieves even more information than what was shown in the previous example:
*************************** 1. row ***************************
TRIGGER_CATALOG: NULL
TRIGGER_SCHEMA: chapter33
TRIGGER_NAME: au_reassign_ticket
EVENT_MANIPULATION: UPDATE
EVENT_OBJECT_CATALOG: NULL
EVENT_OBJECT_SCHEMA: chapter33
EVENT_OBJECT_TABLE: technicians
ACTION_ORDER: 0
ACTION_CONDITION: NULL
ACTION_STATEMENT: begin
if NEW.available = 0 THEN
UPDATE tickets SET technician_id=0 WHERE technician_id=NEW.id;
END IF;
END
ACTION_ORIENTATION: ROW
ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW
CREATED: NULL
SQL_MODE: STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
DEFINER: root@localhost
CHARACTER_SET_CLIENT: latin1
COLLATION_CONNECTION: latin1_swedish_ci
DATABASE_COLLATION: latin1_swedish_ci
Of course, the beauty of querying the INFORMATION_SCHEMA database is that it's so much more flexible than using SHOW. For example, suppose you are managing numerous triggers and want to know which ones triggered after a statement:
SELECT trigger_name FROM INFORMATION_SCHEMA.triggers WHERE action_timing="AFTER"
Or perhaps you'd like to know which triggers were executed whenever the technicians table was the target of an INSERT, UPDATE, or DELETE query:
mysql>SELECT trigger_name FROM INFORMATION_SCHEMA.triggers WHERE
->event_object_table="technicians"
Modifying a Trigger
At the time of writing, there was no supported command or GUI application available for modifying an existing trigger. Therefore, perhaps the easiest strategy for modifying a trigger is to delete and subsequently re-create it.
Deleting a Trigger
It's conceivable, particularly during a development phase, that you'll want to delete a trigger, or remove it if the action is no longer needed. This is accomplished by using the DROP TRIGGER statement, the prototype of which follows:
DROP TRIGGER [IF EXISTS] table_name.trigger_name
For example, to remove the au_reassign_ticket trigger, execute the following command:
DROP TRIGGER technicians.au_reassign_ticket;
You need the TRIGGER or SUPER privilege to successfully execute DROP TRIGGER.
Caution When a database or table is dropped, all corresponding triggers are also deleted.
Integrating Triggers into Web Applications
Because triggers occur transparently, you really don't need to do anything special to integrate their operation into your Web applications. Nonetheless, it's 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. In this section you'll learn how to implement the helpdesk application first depicted earlier, in the section "Taking Action After an Event."
To begin, if you haven't done so already, go ahead and create the two tables (technicians and tickets) depicted in the earlier section, and add a few appropriate rows to each, making sure that each tickets.technician_id matches a valid technicians.technician_id. Next, create the au_reassign_ticket trigger as previously described.
Recapping the scenario, submitted helpdesk tickets are resolved by assigning each to a technician. If a technician is out of the office for an extended period of time, say due to a vacation or illness, he is expected to update his profile by changing his availability status. The profile manager interface looks similar to that shown in Figure 33-1.
Figure 33-1. The helpdesk account interface
When the technician makes any changes to this interface and submits the form, the code presented in Listing 33-1 is activated.
Listing 33-1. Updating the Technician Profile
<?php
// Connect to the MySQL database
$mysqli = new mysqli("localhost", "websiteuser", "secret", "helpdesk");
// Assign the POSTed values for convenience
$email = htmlentities($_POST['email']);
$available = htmlentities($_POST['available']);
// Create the UPDATE query
$query = "UPDATE technicians SET available='$available' WHERE email='$email'";
// Execute query and offer user output
if ($mysqli->query($query)) {
echo "<p>Thank you for updating your profile.</p>";
if ($available == 0) {
echo "<p>Your tickets will be reassigned to another technician.</p>";
}
} else {
echo "<p>There was a problem updating your profile.</p>";
}
?>
Once this code has been executed, return to the tickets table and you'll see that the relevant tickets have been unassigned.
Summary
This chapter introduced triggers, a feature new to MySQL 5. 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. An introduction to MySQL's trigger implementation was offered, followed by coverage of how to integrate these triggers into your PHP applications.
The next chapter introduces views, yet another feature new to MySQL 5.