Action Automation with MySQL Triggers

Much of the code we write is to perform an action. Whether it’s for database querying, file manipulation, data handling, etc., it’s all required to make our scripts function to their set purpose. But have you ever noticed the amount of code you are having to write out sometimes to verify some previous action? One of my recent projects involved a rather cumbersome problem that led me to using countless queries just to make sure all of the data was up to sync throughout my tables after each action. It was far from elegant, and what should have been a rather simple script had turned into a convoluted page of queries. This was not viable from a maintenance standpoint and was a complete nightmare when I wanted to update part of the page’s functionality. It’s here where MySQL triggers came into my project. By making MySQL do more work through triggers, the PHP side of my project was greatly simplified. So, it is the intention of this article to give you some insight into the creation and usage of MySQL triggers, so that by the end of this reading you can make use of them in your own projects.

What are They?

Triggers were introduced in MySQL version 5.0.2 and are just one of MySQL’s added functionality to help make our lives easier as developers. They’re automatically invoked before or after an action (insert, update, delete) has been executed on a table. You’ll need to have the appropriate privileges to create triggers. Prior to MySQL 5.1.6, you needed the SUPER privilege, but this changed in 5.1.6, to where you’ll need the TRIGGER privilege. Generally no shared hosting plan will allow you SUPER because of how easily it can be abused, so you may only be able to use these on a server where you have more authority, like a (virtual) dedicated server or your localhost, depending on the version of MySQL you’re using. Here are some other quick notes regarding triggers:

• They must have unique (case-insensitive) names in the database they have been created in.
• Only one trigger with the same event (update/insert/delete) and timing (before/after) is allowed per table.
• Upon table deletion, the triggers associated with it are also dropped.
• You are unable to explicitly alter a trigger with an ALTER statement (unlike with events). You’ll need to drop the trigger and recreate it.
• Triggers are only fired when a raw SQL statement has been executed; foreign key relationship deletions, for example, will not activate a trigger.

Now let’s take a closer look at the basic syntax of a trigger by stripping it down to its raw form:

CREATE TRIGGER TrigName [BEFORE|AFTER] [INSERT|UPDATE|DELETE] ON tableName
FOR EACH ROW
BEGIN
     #action(s) to perform
END

When creating a trigger, you can choose whether it will fire off before or after an action has occurred; which one you choose will be entirely dependent upon the situation you’re using them in. If you are looking to modify the incoming data going into your database, then BEFORE is needed. If however you’re looking to perform an action because of a previous one, then the AFTER statement should be used. The action that will fire off a trigger can either be INSERT, UPDATE, or DELETE because these are the only three statements that will cause modification to the data inside the table.

Applying a Trigger to a Situation

There are a number of situations where triggers can come in handy. One well-known usage is to maintain data integrity throughout a set of tables with the triggered deletion of stale records when a foreign key has not been used. They can also be used for auto-incrementing or decrementing a stats table upon a new insertion/deletion, logging changes made to data inside a database, keeping tables in sync with other tables, and so on. For this article we’ll be using them to preprocess some calculations. The scenario is that we have a company which rents its hall at a price of £30 per hour. They log the name, start time, and end time of each event held there inside of the events table, and then calculate the time and fees due in the revenue table. The event’s name and start time is inserted into the events table initially to pre-book the hall, and then it’s only after the event has finished that the rental cost is updated upon the row. The duration time of the event needs to be calculated (in minutes), where the start time will be subtracted from the end time, and then the rental fees will be calculated by multiplying the total time by 0.5 (£30 per hour is 50p per minute). We can either perform the calculations for the event duration and fees with PHP (by selecting the inserted data, calculating the duration time and rental fees, and then inserting into or updating the revenues table) upon updating the event’s information, or we can simply use a trigger to automate this process and cut out some PHP. Let’s set up the two basic tables and insert some dummy booking data into events to start things off.

CREATE TABLE events (
    id INTEGER NOT NULL AUTO_INCREMENT,
    event_name VARCHAR(50) NOT NULL,
    event_start TIMESTAMP NOT NULL DEFAULT 0,
    event_end TIMESTAMP NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE revenue (
    id INTEGER NOT NULL AUTO_INCREMENT,
    event_id INTEGER NOT NULL,
    hire_time INTEGER NOT NULL,
    hire_fees FLOAT NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE,
    UNIQUE (event_id)
)ENGINE=INNODB;

INSERT INTO events VALUES
    (NULL, 'Birthday Party', '2012-11-08 14:30:00', 0),
    (NULL, 'Wedding', '2012-12-02 13:00:00', 0);

Once we have setup the two tables, we can move on to creating the trigger which we’ll call CostCalc . The trigger is set to fire after an update on the events table has occurred which will then perform the calculation stated earlier. It then inserts into, or updates (if a pre-existing event ID is already set) the revenue table.

DELIMITER ^^
CREATE TRIGGER CostCalc AFTER UPDATE ON events
FOR EACH ROW
BEGIN
    DECLARE rows BOOL DEFAULT 0;
    DECLARE time INT DEFAULT 0;

    SELECT COUNT(id) INTO rows FROM events
        WHERE id = NEW.id
        AND (OLD.event_start != NEW.event_start
            OR OLD.event_end != NEW.event_end)
        AND NEW.event_end != 0;

    IF(rows = 1) THEN
        SET time = TIMESTAMPDIFF(MINUTE, NEW.event_start, NEW.event_end);
        REPLACE INTO revenue VALUES (NULL, NEW.id, time, time * 0.5);
    END IF;
END
^^

The first thing we need to do when creating a trigger (likewise with events and stored routines) is to specify a new delimiter which signifies the end of the trigger. This is done with the DELIMITER keyword, followed by a custom symbol (or symbols), and is required to execute the trigger as a whole rather than MySQL just executing the statements inside individually. We then specify the trigger name, its timing, event, and upon what table it’ll be set up to fire on. In this example, we timed the trigger to act AFTER the UPDATE statement has occurred because we only want to execute the trigger after a successful update; otherwise we’d be duplicating the previous record of that event. Next we use the BEGIN...END compound statement to house the trigger’s functionality. The trigger’s body starts by declaring two variables: rows and time. We select the number of rows from the events table where the ID references the row that has just been modified, and where either (or both) of the event_start and event_end times have been modified, as well as where the event end time does not equal zero. This is to clarify whether the updated row’s information actually needs anything done to the revenue table because it’s only with these changes that the rental fees are able to be calculated. Once we know that we have the go ahead to calculate the time and fees, we set the time variable to equal the number of minutes from the event’s start to finish columns. By multiplying this number by 0.5 we also get the cost of the rental. Because the event_id column is unique, we are only able to have one ID corresponding to the events table; and so we use REPLACE to update either a pre-existing row in the table with the new data, or insert the new row if it doesn’t exist already. Inside the MySQL statements, you may also have noticed the keywords OLD and NEW being used in the above SELECT and REPLACE statements, as well as in the expression for the time variable’s value. When you use the two will depend upon the event of your situation, as well as the timing of your trigger to fire at.

• The NEW keyword is used to access incoming data into the database. This is only available on INSERT and UPDATE statements.
• The OLD keyword is used to access the current data inside the record before any modifications have been made to it. This is only available on UPDATE and DELETE statements.

The corresponding PHP script that will be used to set off our trigger will include a class (called EventHandler), and our client calling code. The class will connect to our MySQL database via PDO, and will contain one method, updateEvent(), which will be invoked when an event’s content needs to be updated.

<?php
class EventHandler
{
    protected $db;

    public function __construct(PDO $db) {
        $this->db = $db;
        $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    public function updateEvent($type, $param, $id) {
        if (!in_array($type, array('name', 'start', 'end'), TRUE)) {
            throw new InvalidArgumentException(
                'No such column type exists.'
            );
        }

        $query = $this->db->prepare(
            "UPDATE events SET event_{$type} = :param WHERE id = :ID"
        );
        $query->bindParam(':param', $param, PDO::PARAM_STR);
        $query->bindParam(':ID', $id, PDO::PARAM_INT);
        $query->execute();

        if($query->rowCount() !== 1) {
            throw new InvalidArgumentException(
                'No such event ID exists.'
            );
        }
    }
}

$dsn = 'mysql:dbname=events';
$dbuser = 'dbuser';
$passwd = 'dbpassword';
$settings = array(PDO::MYSQL_ATTR_FOUND_ROWS => TRUE);

$eventHandler = new EventHandler(new PDO($dsn, $dbuser, $passwd, $settings));

// will not cause any inserts or updates to the revenue table
$eventHandler->updateEvent('name', 'Auction', 1);
$eventHandler->updateEvent('start', '2012-11-10 14:30:00', 1);

// causes a new insertion into the revenue table
$eventHandler->updateEvent('end', '2012-12-02 20:30:00', 2);
// causes an update on the revenue table
$eventHandler->updateEvent('end', '2012-12-02 21:30:00', 2);

We begin by creating our EventHandler class, where the property $db is defined to hold an instance of the PDO class which is set through the constructor method. We then move on to making our updateEvent() method with three defined arguments. The first argument specifies the column we are wanting to update in our events table, and is allowed one of three values: name, start, end. The second argument holds the value to insert into, or update the current value of the column; whilst the third argument holds the ID of the tuple to update. After ensuring the column name is valid, we query our table through parametrized queries, and finally check if there are any rows updated. After creating the class, we go on to calling it. We pass the instantiation of the PDO object through to the EventHandler class as a parameter. The updateEvent() method is then called four times with varying values to show how our trigger will react to the updates made on our events table. The former two updates will not cause our trigger to insert or update a row because we still don’t have the required information to calculate the event’s duration time and cost of renting fees. All we’ve done is update the event name and postponed its start time by two days. The next two updates however will need our trigger’s functionality because the first update defines an end time, and the second update re-defines the end time to one hour later, causing a change in the duration time and therefore in the rent fees. This is where our REPLACE statement is required, because of the constraint we’ve placed onto the table upon creation, we can only have one record per event ID.

Closing Remarks

When put to good use, MySQL triggers can have not only a positive impact on your site’s performance, but will also save you from writing out numerous lines of PHP to handle such actions. I hope you find them as useful in your projects as I have in mine, so feel free to get trigger happy! Image via Fotolia

Frequently Asked Questions on Action Automation with MySQL Triggers