© Frank M. Kromann 2018
Frank M. KromannBeginning PHP and MySQLhttps://doi.org/10.1007/978-1-4302-6044-8_30

30. MySQL Triggers

Frank M. Kromann1 
(1)
Aliso Viejo, CA, USA
 

A trigger is a task that executes in response to some predefined database event, such as after a new row is added to a particular table. 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 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. I will then discuss 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. Much of this 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 product table includes the identifier of a manufacturer whose information already resides in the manufacturer table.

  • Ensuring database integrity by cascading changes throughout a database, such as removing all products associated with a manufacturer that 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. When possible, it’s preferable to carry out some of these tasks automatically on the server side, regardless of which type of application is interacting with the database. Database triggers give you that choice.

Why Use Triggers?

Triggers have many purposes, including:

  • Audit trails : Suppose you are using MySQL to log Apache traffic (possibly using the Apache mod_log_sql module) but you also want to create an additional special logging table that lets you 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. Databases that supports foreign key constraints can handle integrity enforcements without triggers. Maintaining referential integrity means making sure no reference points to a record in another (or the same) table if that record is deleted. Foreign key is the term used for a column that identifies a key from another table and thus links the two tables together.

The utility of triggers stretches far beyond these purposes. Suppose you want to update the corporate website 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 when 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 food distributor requires that at least $10 of coffee be purchased before it will process the transaction. If a customer 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 to take a vacation or recover from an illness. 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. This is a great scenario in which to use a trigger.

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 (making sure all keys points to existing records in other tables), 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 version 5.0.2 added support for triggers, with some limitations. For instance, as of the time of this writing, the following deficiencies exist:

  • TEMPORARY tables are not supported: A trigger can’t be used in conjunction with a TEMPORARY table.

  • Views are not supported: A trigger can’t be used in conjunction with a view (introduced in the next chapter).

  • The MySQL database does not allow triggers: Tables created in the mysql database will not allow creation of triggers.

  • 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, however, execute stored routines within a trigger, provided they don’t return result sets, as well as the SET command.

  • 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.

This might seem limiting, but triggers still provide a powerful way of implementing business logic. If you have multiple users/systems interacting directly with the database and you don’t want each of them to implement certain business logic, you can use triggers. A different way to solve this is to create APIs where the logic is implemented and only allow the users to interact with the API and not directly with the database. An advantage of this approach is the freedom you get to alter the schema when needed as long as your API continues to work the same way.

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
   [{ FOLLOWS | PRECEDES } <other_trigger_name>]
   <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.

The following example implements the helpdesk trigger 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=null 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 null 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.

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 quickly determine the purpose of each. For example, you might consider prefixing each trigger title with one of the following strings, as has been done in the trigger-creation example:

  • ad: Execute trigger after a DELETE query has taken place

  • ai: Execute trigger after an INSERT query has taken place

  • au: Execute trigger after an UPDATE query has taken place

  • bd: Execute trigger before a DELETE query has taken place

  • bi: Execute trigger before an INSERT query has taken place

  • bu: Execute trigger before an UPDATE query has taken place

Viewing Existing Triggers

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 | WHERE 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)
You might want to view the trigger creation statement. To view the trigger creation syntax, use the SHOW CREATE TRIGGER statement, like this:
mysql>SHOW CREATE TRIGGER au_reassign_ticketG
*************************** 1. row ***************************
               Trigger: au_reassign_ticket
              sql_mode:
SQL Original Statement: CREATE DEFINER=`root`@`localhost` TRIGGER au_reassign_ticket
AFTER UPDATE ON technicians
FOR EACH ROW
BEGIN
   IF NEW.available = 0 THEN
      UPDATE tickets SET  technician_id=null WHERE  technician_id=NEW.id;
   END IF;
END
  character_set_client: latin1
  collation_connection: latin1_swedish_ci
    Database Collation: latin1_swedish_ci

An alternative approach to learning more about a trigger involves querying the INFORMATION_SCHEMA database.

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
As you can see, the beauty of querying the INFORMATION_SCHEMA database is that it’s so much more flexible than using SHOW TRIGGERS . 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 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.

In the previous sections we have discussed creation and dropping of triggers. This can easily be done from PHP instead of from the command line or a GUI tool. This is because of the nature of SQL. As mentioned before, there are two types of SQL commands. The first one handles the schema objects and the second handles the data in tables. Because of their nature, there is no difference in issuing a command that creates a table or trigger compared to a command that inserts, updates, or deletes rows in a table. Listing 30-1 shows how PHP can be used to create a trigger.
<?php
   // Connect to the MySQL database
   $mysqli = new mysqli("localhost", "websiteuser", "secret", "helpdesk");
// Create a trigger
$query = <<<HEREDOC
DELIMITER //
CREATE TRIGGER au_reassign_ticket
AFTER UPDATE ON technicians
FOR EACH ROW
BEGIN
   IF NEW.available = 0 THEN
      UPDATE tickets SET  technician_id=null WHERE  technician_id=NEW.id;
   END IF;
END;//
HEREDOC;
$mysqli->query(($query);
?>
Listing 30-1

Create trigger

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 “Taking Action After an Event” section.

To begin, if you haven’t done so already, go ahead and create the two tables (technicians and tickets) depicted in the earlier section. 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, he is expected to update his profile by changing his availability status. The profile manager interface looks similar to that shown in Figure 30-1.
../images/314623_5_En_30_Chapter/314623_5_En_30_Fig1_HTML.jpg
Figure 30-1

The helpdesk account interface

When the technician makes any changes to this interface and submits the form, the code presented in Listing 30-2 is activated.
<?php
   // Connect to the MySQL database
   $mysqli = new mysqli("localhost", "websiteuser", "secret", "helpdesk");
   // Assign the POSTed values for convenience
   $options = array('min_range' => 0, 'max_range' => 1);
   $email = filter_var($_POST['email'], FILTER_VALIDATE_EMAIL);
   $available = filter_var($_POST['available'], FILTER_VALIDATE_INT, $options);
   // Create the UPDATE query
   $stmt = $mysqli->prepare("UPDATE technicians SET available=? WHERE email=?");
   $stmt->bind_param('is', $available, $email);
   // Execute query and offer user output
   if ($stmt->execute()) {
      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>";
   }
?>
Listing 30-2

Updating the Technician Profile

Once this code has been executed , return to the tickets table and you’ll see that the relevant tickets have been unassigned.

Summary

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, a powerful feature that allows you to essentially create easy-to-remember aliases for otherwise long and complex SQL statements.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.133.109.30