MySQL Triggers:
Auto-generate additional information in the database

MySQL Triggers are one of the newer features in MySQL that are helping to make it a viable alternative for large enterprise applications. Not too long ago, those who made their livings using big commercial databases like Oracle and DB2 pointed out that MySQL was a nice, fast little database but lacked important feature like stored procedures, transactions, and triggers. As of version 5.0 of MySQL, these features can be crossed off of that list.

So, what are MySQL triggers, and why does MySQL's ability to use them make it more attractive to serious database users?

Simply put, triggers are small programs that are stored in the database itself, and are activated by database events which often originate at the application layer. These precipitating database events are UPDATE, DELETE or INSERT queries. The trigger itself may execute before or after the query that initiates it.

Triggers are often used to maintain the integrity of data across tables of an application. When a user on a website makes a purchase, for example, the first action that occurs in the database may be that a credit is inserted into an accounting table.

By way of a trigger this action could initiate a chain reaction of events in other tables throughout the application. The product count of an item could be decremented in an inventory table, a debit deducted from a customer's account balance in another table, a store credit applied to yet another table.

You may say that you have been doing this all along in your applications using PHP or Perl or Python or ASP code. What's the big deal about using MySQL triggers? Well, there are some advantages to using triggers over application code for maintaining integrity of data across tables. A trigger generally performs the types of tasks described faster than application code, and and can be activated easily and quickly behind the scenes and does not need to be a part of your application code.

This saves time and spares you from redundant coding. If you ever port your application to another language, chances are your triggers can stay in place without modification, along with your tables and other database objects.

To demonstrate how MySQL triggers work, let's set up two simple tables on a database we'll call “sales_records” that have data that is interdependent.

Imagine a database that tracks the sales records of three salespeople at a department store. They work in the electronics department selling things like TVs , stereos, and MP3 players.

We have the main table that keeps a record of each sale made. It records the amount of the sale (sale_amt), the date (date), the name of the salesman (name), his id number (employee_id), and the product id (prod_id). We'll call this table (cleverly enough) “sales”.

In the second table, we want to keep some data that will allow us to easily keep track of how each salesperson is doing. It will include the salesperson's id (employee_id), name (name), total number of sales (total_sales), and a column that keeps each salesperson's average amount per sale (ave_sale). We want to see who's moving the high-end items. We'll call this table “performance”.

Now comes the hard part. As I mentioned, triggers are database objects just as tables are. Triggers, however, are able to execute procedural code that modifies data in your tables. In this case, we want our trigger to fire before any INSERT statement that executes in the sales table. When a sale record is inserted in the sales table, the salesperson's totals must be updated in the performance table.

The following code can be typed in your favorite text editor and pasted into your konsole at the MySQL prompt. Before you do that though, you want to execute this line:

mysql: Delimiter $$

Our procedural code uses semicolons at the end of statements, so we need to set a different delimiter to let MySQL know when our code block is over, and so that it doesn't stop processing our block when it hits a semicolon. Keep in mind that after you finish your block you will have to set the delimiter back to the semicolon, or end any subsequent commands with the new delimiter. For example if you made errors in your CREATE TRIGGER block and want to delete it, DROP TRIGGER; won't work unless you set the delimiter back to the semicolon.

Here is the code for the trigger:

OK, let's talk about the code. Using the CREATE TRIGGER statement, we've initiated the trigger, naming it 'sales_bi_trg'. MySQL triggers can fire before or after an INSERT, UPDATE or DELETE event. This one fires before any data is inserted in the 'sales' table.

The FOR EACH ROW clause signifies that the block will act on each row that meets the criteria of our SQL statements. The keywords BEGIN and END enclose the trigger statements that will execute when the trigger fires.

There are two variables declared. The first is 'num_row' which checks to see if the employee has who has made the sale that is to be entered, has had a sale entered in the performance table previously. If there are no employee_id's that match, then this is the employee's first sale, and this meets the 'ELSE' condition of our “IF' statement.

This data will be entered as an insert in the performance table rather than an update. If the 'num_row' is greater than 0, then the performance table will be updated. The second variable, 'tot_rows', is a count of how many sales the employee has in the 'sales' table.

This value is used to calculate the employee's average sale. The count is being done before the sale is inserted in the sale table, so we have to add one to it. When the 'performance' table is updated the average sale = total_sales/(tot_rows+1).

If our MySQL trigger is working correctly, the 'performance' table will keep a running total of each salespersons total sales, and also the average amount of their total sales. It will do this independently of your application code and be portable to any application platform.

To give it a whirl, insert some data into the 'sales' table and monitor the content of the 'performance' table. Here is the statement:

Change the numbers and names and try it a few times. (Remember, an employee keeps the same employee_id number for each of his sales.) If you're feeling adventurous, start thinking about how the MySQL trigger would have to be extended to account for UPDATE and DELETE statements on the 'sales' table.

Return to MySQL DBA