MySQL Stored Procedure Programming (2009)
Part II. Stored Program Construction
Chapter 11. Triggers
Database triggers are stored programs that are executed in response to some kind of event that occurs within the database. In the current MySQL implementation of triggers, triggers fire in response to a DML statement (INSERT, UPDATE, DELETE) on a specified table.
Triggers are a powerful mechanism for ensuring the integrity of your data, as well as a useful means of automating certain operations in the database, such as denormalization and audit logging.
Creating Triggers
Triggers are created with the—you guessed it—CREATE TRIGGER statement, which has the following syntax:
CREATE [DEFINER={user|CURRENT_USER}] TRIGGER trigger_name
{BEFORE|AFTER}
{UPDATE|INSERT|DELETE}
ON table_name
FOR EACH ROW
trigger_statements
Let's look at each part of the CREATE TRIGGER statement in turn:
DEFINER ={user | CURRENT_USER }
Controls the account that will be used to check privileges when the trigger is invoked. The default of CURRENT_USER indicates that the trigger statements will run with the authority of the account that issued the CREATE TRIGGER statement, rather than the account that issued the DML that caused the trigger to fire.
trigger_name
The trigger name follows the normal conventions for MySQL's naming of database objects. While you can call your trigger virtually anything, we recommend that you adopt a predictable naming convention. There can be only one trigger for any combination of BEFORE or AFTER andUPDATE, INSERT, or DELETE (for example, there can be only one BEFORE UPDATE trigger on a table), so a sensible convention might result in triggers being given names such as table_name _bu (for a BEFORE UPDATE trigger) or table_name _ai (for an AFTER INSERT trigger).
BEFORE|AFTER
Specifies whether the trigger fires before or after the DML statement itself has been executed. We'll discuss the implications of this shortly.
UPDATE|INSERT|DELETE
Defines the DML statement to which the trigger is associated.
ON table_name
Associates the trigger with a specific table.
FOR EACH ROW
This clause is mandatory in the initial MySQL implementation. It indicates that the trigger will be executed once for every row affected by the DML statement. The ANSI standard also provides for a FOR EACH STATEMENT mode, which might be supported in an upcoming version of MySQL.
trigger_statements
Define the statements that will be executed when the trigger is invoked. If there is more than one statement, then the statements need to be enclosed in a BEGIN-END block.
Prior to MySQL 5.1.6, you needed the SUPER privilege to create a trigger. In 5.1.6 and above, the TRIGGER privilege is required.
Referring to Column Values Within the Trigger
Trigger statements can include references to the values of the columns being affected by the trigger. You can access and sometimes modify the values of these columns.
To distinguish between the values of the columns "before" and "after" the relevant DML has fired, you use the NEW and OLD modifiers. For instance, in a BEFORE UPDATE trigger, the value of the column mycolumn before the update is applied is OLD.mycolumn, and the value after modification is NEW.mycolumn.
If the trigger is an INSERT trigger, only the NEW value is available (there is no OLD value). Within a DELETE trigger, only the OLD value is available (there is no NEW value).
Within BEFORE triggers you can modify a NEW value with a SET statement—thus changing the effect of the DML.
Triggering Actions
Triggers will normally execute in response to the DML statements matching their specification—for instance, BEFORE INSERT will always be invoked in response to an INSERT statement.
However, triggers also fire in response to implicit—as well as explicit—DML. Some statements are capable of generating DML as a side effect of their primary activity. For instance, an INSERT statement that contains an ON DUPLICATE KEY UPDATE clause can issue an implicit UPDATEstatement causing BEFORE UPDATE or AFTER UPDATE triggers to fire. Likewise, the REPLACE statement can cause both INSERT and DELETE triggers to fire (since, for an existing row, REPLACE issues a DELETE followed by an INSERT).
BEFORE and AFTER Triggers
The BEFORE and AFTER clauses determine when your trigger code executes: either before or after the DML statement that causes the trigger to be invoked.
The most significant difference between BEFORE and AFTER triggers is that in an AFTER trigger you are not able to modify the values about to be inserted into or updated with the table in question—the DML has executed, and it is too late to try to change what the DML is going to do.
IF you try to modify a NEW value in an AFTER trigger, you will encounter an error, as shown in Example 11-1.
Example 11-1. AFTER triggers cannot modify NEW values
mysql> CREATE TRIGGER account_balance_au
AFTER UPDATE ON account_balance FOR EACH ROW
BEGIN
DECLARE dummy INT;
IF NEW.balance<0 THEN
SET NEW.balance=NULL;
END IF;
END
$$
ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger
Although you can do pretty much anything you need to do in a BEFORE trigger, you still may wish to use AFTER triggers for activities that logically should occur in a transaction after a DML has successfully executed. Auditing activities, for example, are best executed in an AFTER trigger, since you will first want to make sure that the DML succeeded.
Using Triggers
Triggers can be used to implement a variety of useful requirements, such as automating the maintenance of denormalized or derived data, implementing logging, and validating data.
Maintaining Derived Data
We often need to maintain redundant "denormalized" information in our tables to optimize critical SQL queries. The code to perform this denormalization could be placed within the application code, but then you would have to make sure that any and every application module that modifies the table also performs the denormalization. If you want to guarantee that this code is run whenever a change is made to the table, you can attach that functionality to the table itself, via a trigger.
Let's take a look at an example of the value of denormalized data in our tables. Suppose that we have a table within our database that contains the total sales for all orders from each customer. This allows us to quickly identify our most significant customers without having to do a costly query on the very large sales table.
Unfortunately, we have a variety of order processing systems, not all of which can be modified to maintain this table. So we need a way of making sure that the table is modified every time an INSERT occurs into the sales table. A trigger is an ideal way of maintaining the values in this summary table.
Example 11-2 shows example triggers that maintain the values in the customer_sales_totals table whenever there is an UPDATE, INSERT, or DELETE operation on the sales table.
Example 11-2. Using triggers to maintain denormalized data
DELIMITER $$
CREATE TRIGGER sales_bi_trg
BEFORE INSERTON sales
FOR EACH ROW
BEGIN
DECLARE row_count INTEGER;
SELECT COUNT(*)
INTO row_count
FROM customer_sales_totals
WHERE customer_id=NEW.customer_id;
IF row_count > 0 THEN
UPDATE customer_sales_totals
SET sale_value=sale_value+NEW.sale_value
WHERE customer_id=NEW.customer_id;
ELSE
INSERT INTO customer_sales_totals
(customer_id,sale_value)
VALUES(NEW.customer_id,NEW.sale_value);
END IF;
END$$
CREATE TRIGGER sales_bu_trg
BEFORE UPDATEON sales
FOR EACH ROW
BEGIN
UPDATE customer_sales_totals
SET sale_value=sale_value+(NEW.sale_value-OLD.sale_value)
WHERE customer_id=NEW.customer_id;
END$$
CREATE TRIGGER sales_bd_trg
BEFORE DELETEON sales
FOR EACH ROW
BEGIN
UPDATE customer_sales_totals
SET sale_value=sale_value-OLD.sale_value
WHERE customer_id=OLD.customer_id;
END$$
Implementing Logging
The ability to identify the source and nature of updates to application data is increasingly critical in our security-conscious societies. Indeed, the tracking of database changes is often mandated by government and industry regulations such as Sarbanes-Oxley and HIPAA. Although an application can be designed and implemented such that it performs its own auditing, many organizations require that any database updates—including those performed directly against the database using command-line clients or database utilities—also be logged. Triggers are an ideal way of implementing this kind of logging .
Suppose that we are building a financial application, for which we must track all modifications to a user's account balance. In Chapter 8, we implemented such a scheme using a stored procedure that controlled all account balance transactions. However, triggers provide a superior solution since they will also log any transactions performed outside of the stored procedure.
Example 11-3 shows a trigger that will perform this type of logging for UPDATE statements. In order to ensure universal logging, we would need to create a similar trigger for INSERT and DELETE statements.
Example 11-3. Using triggers to implement audit logging
CREATE TRIGGER account_balance_au
AFTER UPDATE ON account_balance FOR EACH ROW
BEGIN
INSERT into transaction_log
(user_id, description)
VALUES (user( ),
CONCAT('Adjusted account ',
NEW.account_id,' from ',OLD.balance,
' to ', NEW.balance));
END;
Validating Data with Triggers
A typical and traditional use of triggers in relational databases is to validate data or implement business rules to ensure that the data in the database is logically consistent and does not violate the rules of the business or the application. These triggers are sometimes referred to as check constraint triggers .
Data validation triggers may perform tasks such as:
Implementing checks on allowable values for columns
For instance, a percentage value must fall between 0 and 100, a date of birth cannot be greater than today's date, and so on.
Performing cross-column or cross-table validations
For example, an employee cannot be his own manager, a sales person must have an associated quota, and seafood pizzas cannot include anchovies (here the authors must agree to disagree: Guy hates anchovies, while Steven finds them almost a requirement for an enjoyable pizza!).
Performing advanced referential integrity
Referential constraints are usually best implemented using foreign key constraints; sometimes, however, you may have some advanced referential integrity that can only be implemented using triggers. For instance, a foreign key column may be required to match a primary key in one of a number of tables (an arc relationship).
A data validation trigger typically prevents a DML operation from completing if it would result in some kind of validation check failing.
If MySQL 5.0 or 5.1 implemented all ANSI-standard functionality, we would implement such checks in a database trigger by issuing a SIGNAL statement, as shown in Example 11-4.
Example 11-4. ANSI-standard trigger to enforce a business rule
CREATE TRIGGER account_balance_bu
BEFORE UPDATE
ON account_balance
FOR EACH ROW
BEGIN
-- The account balance cannot be set to a negative value.
IF (NEW.balance < 0) THEN
-- Warning! Not implemented in MySQL 5.0...
SIGNAL SQLSTATE '80000'
SET MESSAGE_TEXT='Account balance cannot be less than 0';
END IF;
END;
Unfortunately, MySQL 5.0 and 5.1 do not support the SIGNAL statement; we expect it to appear in version 5.2. Consequently, we do not currently have a standard way of aborting a DML statement that violates a business rule.
Luckily, we can use a variation on the workaround we introduced in Chapter 6 to force a trigger to fail in such a way that it prevents the DML statement from completing and provides a marginally acceptable error message.
In Example 6-19, we introduced a stored procedure—my_signal—that used dynamic SQL to create an "Invalid table name" error condition and embedded an error message of our choosing into that error. Unfortunately, we cannot call the my_signal procedure directly, because triggers are forbidden from executing dynamic SQL. However, we can include very similar logic into the trigger that will have the same effect. Example 11-5 shows a trigger that ensures that there will be no negative account balance. If a negative account balance is detected, the trigger attempts to execute a SELECT statement that references a nonexistent column. The name of the column includes the error message that we will report to the calling program.
Example 11-5. MySQL trigger to perform data validation
CREATE TRIGGER account_balance_bu
BEFORE UPDATE
ON account_balance
FOR EACH ROW
BEGIN
DECLARE dummy INT;
IF NEW.balance<0 THEN
SELECT 'Account balance cannot be less than 0' INTO dummy
FROM account_balance
WHERE account_id=NEW.account_id;
END IF;
END;
Example 11-6 shows how the trigger prevents any updates from proceeding if the end result would be to create an account_balance row with a negative value in the balance column. While the error code is not ideal, and the error message is embedded in another error message, we at least have prevented the UPDATE from creating a negative balance, and we have provided an error message that does include the reason why the UPDATE was rejected.
Example 11-6. Behavior of our data validation trigger
SELECT * FROM account_balance WHERE account_id=1;
+------------+---------+---------------------+
| account_id | balance | account_timestamp |
+------------+---------+---------------------+
| 1 | 800.00 | 2005-12-13 22:12:28 |
+------------+---------+---------------------+
1 row in set (0.00 sec)
UPDATE account_balance SET balance=balance-1000 WHERE account_id=1;
ERROR 1054 (42S22): Unknown column 'Account balance cannot be less than 0' in 'field list'
SELECT * FROM account_balance WHERE account_id=1;
+------------+---------+---------------------+
| account_id | balance | account_timestamp |
+------------+---------+---------------------+
| 1 | 800.00 | 2005-12-13 22:12:28 |
+------------+---------+---------------------+
1 row in set (0.00 sec)
UPDATE account_balance SET balance=500 WHERE account_id=1;
Query OK, 1 row affected (0.15 sec)
Rows matched: 1 Changed: 1 Warnings: 0
SELECT * FROM account_balance WHERE account_id=1;
+------------+---------+---------------------+
| account_id | balance | account_timestamp |
+------------+---------+---------------------+
| 1 | 500.00 | 2005-12-13 22:12:34 |
+------------+---------+---------------------+
1 row in set (0.00 sec)
This trigger can be easily modified to use the SIGNAL statement when it becomes available.
Trigger Overhead
It is important to remember that, by necessity, triggers add overhead to the DML statements to which they apply. The actual amount of overhead will depend upon the nature of the trigger, but—as all MySQL triggers execute FOR EACH ROW—the overhead can rapidly accumulate for statements that process large numbers of rows. You should therefore avoid placing any expensive SQL statements or procedural code in triggers.
We will look at an example of trigger overhead in Chapter 22.
Conclusion
MySQL triggers allow you to execute stored program code whenever a DML statement is issued against a database table. In MySQL 5.0, triggers can be used to automate denormalization or logging.
Implementation of data validation in MySQL triggers is more of a challenge, as in MySQL there is no easy or straightforward way to raise an error condition or abort the transaction when validation fails. This will be remedied when the SIGNAL statement is implemented in MySQL 5.2. In this chapter we presented a workaround that does allow data validation triggers to be created in the interim, although the error text generated is far from ideal.