Ease database management and automate functions with triggers in MySQL

by Contributor Melonfire | Aug 23, 2006 6:46:00 PM

Tags: Databases, database management, Contributor Melonfire, trigger, MySQL, database

Takeaway: In database terminology, a trigger is a stub of code that is automatically activated when a particular database event, such as an INSERT or DELETE, occurs. Triggers are handy for logging, to automatically "cascade" changes down from a single table to other linked tables, or to ensure that table relationships are automatically updated. Get a brief introduction to the uses of the MySQL database trigger, how to view trigger status, and how to delete a trigger once you're done with it. You will also see a common example of how triggers are used in the real world to audit changes made to database records.

In SQL terms, a trigger is "procedural code that is automatically executed in response to certain events on a particular table in a database" (Wikipedia). More simply, it is a stub of code that is automatically activated when a particular database event, such as an INSERT or DELETE, occurs. Triggers are handy for logging, to automatically "cascade" changes down from a single table to other linked tables, or to ensure that table relationships are automatically updated. One example of a trigger might be a code fragment that automatically updates a running total when a new integer value is added to a database field; another might be a block of SQL commands that automatically record the changes made to a particular database table.

Triggers are new in MySQL 5.x, and have been gradually improving as new versions of the 5.x code tree have emerged. In this document, I'll provide you with a brief introduction to defining and using a trigger, viewing trigger status, and deleting a trigger once you're done with it. I'll also show you a common example of how triggers are used in the real world, to audit changes made to database records.

A simple example

The best way to understand how MySQL triggers work is with a simple (albeit contrived) example. Begin by creating two single-field tables, one containing a list of names (table name: data), and the other containing a count of the total number of characters inserted (table name: chars). The idea here is to define a trigger on the data table, such that every time a new name is inserted into it, the running total in the chars table is automatically updated with the number of characters in the newly-inserted record. (See Listing A)

Listing A

mysql> CREATE TABLE data (name VARCHAR(255));
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE chars (count INT(10));
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO chars (count) VALUES (0);
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TRIGGER t1 AFTER INSERT ON
data FOR EACH ROW UPDATE chars SET count = count + CHAR_LENGTH(NEW.name);
Query OK, 0 rows affected (0.01 sec)

The key to understanding this is the CREATE TRIGGER command, used to define a new database trigger. This command sets up a new trigger, imaginatively named t1, that is activated every time a new record is inserted into the data table.

There are two important clauses in this trigger:

• The AFTER INSERT clause indicates that the trigger will activate after the new record has been inserted into the data table.
• The UPDATE chars SET count = count + CHAR_LENGTH(NEW.name) clause indicates the SQL command that the trigger will execute when it is activated. In this case, the command involves updating the chars.count column with the number of characters in the newly-inserted data.name field. This information is obtained with the built-in MySQL CHAR_LENGTH() function.

Also worth noting is the NEW keyword, prefixed to the field name in the source table. This keyword indicates that the trigger should take account of the new value of the field (that is, the value that has just been inserted into the field). MySQL also supports a corresponding OLD prefix, which can be used to refer to the previous value of the field.

You can check if your trigger is active via a call to the special SHOW TRIGGERS command, as shown in Listing B.

Listing B

mysql> SHOW TRIGGERS\G
*************************** 1. row ***************************
  Trigger: t1
    Event: INSERT
    Table: data
Statement: UPDATE chars SET count = count + CHAR_LENGTH(NEW.name)
   Timing: AFTER
  Created: NULL
 sql_mode:
1 row in set (0.01 sec)

With the trigger in place, all that's left is to test it out. Try inserting a couple of records into the data table:

mysql> INSERT INTO data (name) VALUES ('Sue'), ('Jane');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

And then check the chars table to see if the trigger did what it was supposed to:

mysql> SELECT * FROM chars;
+-------+
| count |
+-------+
|     7 |
+-------+
1 row in set (0.00 sec)

As you can see, the INSERT into the data table activated our trigger, which counted the number of characters in the inserted records and stored that count in the chars table. If you add more records to the data table, the chars.count value will increase correspondingly.

Once you're done playing with your trigger, it's easy to remove it with the DROP TRIGGER command.

mysql> DROP TRIGGER t1;
Query OK, 0 rows affected (0.00 sec)

Note: Ideally, you'd also want a reverse trigger, that decremented the character count every time a record was deleted from the source table. This is easy to do, and you can try it for yourself as an exercise. Hint: one way to do it is by using a BEFORE DELETE ON clause.

A real-world example: Audit trails with triggers

Now that you know the basics, let's try a slightly more complex example. A common use of triggers is to set up automatic "audit trails" to record the changes made by different users to a database. To see how such an audit trail might work in practice, consider the following table (table name: accounts), which lists balances for a user's three bank accounts. (Table A)

Table A

mysql> SELECT * FROM accounts;
+----+------------+---------+
| id | label      | balance |
+----+------------+---------+
|  1 | Savings #1 |     500 |
|  2 | Current #1 |    2000 |
|  3 | Current #2 |    3500 |
+----+------------+---------+
3 rows in set (0.00 sec)

Now, I'd like to set up an audit trail to track changes made to this table. This trail should reflect every change made to the table, and indicate both the user who initiated the change and the time at which the change was made. To store this information, I need to create a new table (table name: audit) as shown below. (Listing C)

Listing C

mysql> CREATE TABLE audit (id INT(7), balance FLOAT, user VARCHAR(50)
NOT NULL, time TIMESTAMP NOT NULL);
Query OK, 0 rows affected (0.09 sec)

Next, I'll define a trigger on the accounts table. (Listing D)

Listing D

mysql> CREATE TRIGGER t1 AFTER UPDATE ON accounts
FOR EACH ROW INSERT INTO audit (id, balance, user, time)
VALUES (OLD.id, NEW.balance, CURRENT_USER(), NOW());
Query OK, 0 rows affected (0.04 sec)

If you've followed along so far, this should be easy to read. Each time the accounts table experiences an UPDATE, the trigger INSERTs the id of the corresponding record, the new balance, the current time, and the name of the currently logged-in user into the audit table.

Next, check that your trigger has been activated:

mysql> SHOW TRIGGERS \G
*************************** 1. row ***************************
  Trigger: t1
    Event: UPDATE
    Table: accounts
Statement: INSERT INTO audit (id, balance, user, time) VALUES (OLD.id, NEW.balance, CURRENT_USER(), NOW())
   Timing: AFTER
  Created: NULL
 sql_mode:
1 row in set (0.01 sec)

And then take it for a spin (Listing E):

Listing E

mysql> UPDATE accounts SET balance = 500 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE accounts SET balance = 900 WHERE id = 3;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> UPDATE accounts SET balance = 1900 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Notice that the changes to the accounts table have been recorded to the audit table, from where they can be easily retrieved in the event of any problem in the future.

mysql> SELECT * FROM audit;
+------+---------+----------------+---------------------+
| id   | balance | user           | time                |
+------+---------+----------------+---------------------+
|    1 |     500 | root@localhost | 2006-04-22 12:52:15 |
|    3 |     900 | root@localhost | 2006-04-22 12:53:15 |
|    1 |    1900 | root@localhost | 2006-04-22 12:53:23 |
+------+---------+----------------+---------------------+
3 rows in set (0.00 sec)

As the above examples illustrate, triggers are a powerful new feature, which can help add a degree of automation to your RDBMS. Try them out and see for yourself!