MySQL Stored Procedure Programming

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