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.
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
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
DELETE operation on the
sales table. ...