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.
11.2.1. 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$$ |
11.2.2. 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 updatesincluding those performed directly against the database using command-line clients or database utilitiesalso 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; |
11.2.3. 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 proceduremy_signalthat 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.