Skip to Main Content
MySQL Stored Procedure Programming
book

MySQL Stored Procedure Programming

by Guy Harrison, Steven Feuerstein
March 2006
Intermediate to advanced content levelIntermediate to advanced
640 pages
17h 8m
English
O'Reilly Media, Inc.
Content preview from 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. ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

MySQL Concurrency: Locking and Transactions for MySQL Developers and DBAs

Jesper Wisborg Krogh
MySQL 8 Administrator???s Guide

MySQL 8 Administrator???s Guide

Chintan Mehta, Hetal Oza, Subhash Shah, Ravi Shah
MySQL Cookbook, 4th Edition

MySQL Cookbook, 4th Edition

Sveta Smirnova, Alkin Tezuysal
Learning MySQL, 2nd Edition

Learning MySQL, 2nd Edition

Vinicius M. Grippa, Sergey Kuzmichev

Publisher Resources

ISBN: 0596100892Supplemental ContentErrata Page