Triggers: An Introduction

22 07 2010

Triggers is one of the subjects you need to master in order to become certified as a MySQL Developer (see http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getpage?page_id=41&p_exam_id=1Z0_872). Whereas only 5% of Exam II is devoted to this subject, just about every PHP Developer interview I’ve had recently seems to include a question about them.

If you’ve ever worked on the front-end with JavaScript and event-handlers then you’ll find catching on to triggers is fairly easy since they are analogous to JavaScript’s event-handling user-defined functions. A major difference is that you can specify whether a trigger’s execution occurs before or after an event. Also, triggers are restricted to effecting only table rows.

The formal definition of a trigger according to http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html:

A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table

As a programmer, when I hear the word object, I tend to have tunnel vision and only think about objects in terms of OOP. What the above definition really means is that a trigger is a specific database entity that you use in connection with tables. Or, you could think of a trigger as a user-defined command based on using one or more SQL commands that you program to occur before or after issuing an INSERT, UPDATE, or DELETE command.

You may wonder why anyone would wish to create one. It basically allows you to have a 2-for-1 deal. You get two commands being carried out while needing only to explicitly invoke one. So, if you needed to update some information and wanted to keep track of your changes, you could automatically save the old data by means of using a trigger when you issue an UPDATE command. Let’s try that.

I’m going to use the same schema that I used previously when discussing joins and in this case I’m going to elevate someone’s rank and archive the previous record.

I add one more table to the schema to keep track of changes, as follows:

CREATE TABLE IF NOT EXISTS `grp_audit_table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`rank` varchar(255) NOT NULL,
`serialno` text NOT NULL,
`score` int(11) NOT NULL,
`changedon` datetime NOT NULL,
`action` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='for zce test' AUTO_INCREMENT=13 ;

I also create a trigger that will automatically execute when I update the grp_table and populate the above table with the record prior to changing it, as follows:

DROP TRIGGER IF EXISTS `zce`.`before_grp_table_update`;
DELIMITER //
CREATE TRIGGER `zce`.`before_grp_table_update` BEFORE UPDATE ON `zce`.`grp_table`
FOR EACH ROW
BEGIN
INSERT INTO grp_audit_table
SET action = 'update',
rank = OLD.rank,
name = OLD.name,
score = OLD.score,
serialno = OLD.serialno,
changedon = NOW();
END
//
DELIMITER ;

The above code checks to see if a trigger was previously created with the same name and if so it eliminates it with the DROP command. Since triggers may be triggered by INSERT, UPDATE and DELETE, you can only get rid of a trigger by issuing the DROP command.

The DELIMITER command is necessary because within the BEGIN and END code block we use an INSERT statement that needs to end in a semicolon. So, to distinguish which is the command to trigger and which code is creating the trigger, we use two different delimiters and then restore the default delimiter once we’ve finished defining the CREATE TRIGGER command.

The syntax for the CREATE TRIGGER command requires that you specify a database and a name for the trigger. You also need to specify if it should occur before or after an event and what that event is. Lastly, you need to associate the trigger with a table.

FOR EACH ROW is something that you will put in when you want the trigger iterate over the table’s records. Otherwise, you can leave it out (see http://forge.mysql.com/wiki/Triggers).

BEGIN and END are what you’d use when you have multiple SQL statements for the trigger to execute. In this case there is only one so I could have left them out, but I think retaining them makes the code more readable.

If you look at the INSERT statement it contains a mysterious OLD reference which is actually a MySQL keyword. It’s basically shorthand for allowing you to refer to the data in in grp_table before UPDATE takes effect. So, instead of typing grp_table.rank you can use the more descriptive shorthand of OLD.rank.

Let’s try out the trigger. Note, we will not be manually invoking it. It’s execution is automatic and will occur when we provide an UPDATE command, and take effect before the UPDATE, as follows:

UPDATE grp_table SET rank = "General" WHERE serialno = "a823bc457"

You can check that the record was updated in grp_table with the following query:

SELECT *
FROM `grp_table`
WHERE name = "Sharon"
LIMIT 0 , 30

You can also view the grp_audit_table’s latest entry, as follows:
SELECT name, rank, score, changedon,
ACTION
FROM `grp_audit_table`
WHERE name = "Sharon"
LIMIT 0 , 30

which yields a result as follows:

name rank score changedon action
Sharon Captain 80 2010-07-22 15:30:27 update

revealing the record prior to the update.

If you forget what triggers you have created, you can issue the SHOW TRIGGERS command, provided that you have selected a database. In the event that you wish to know about all the triggers that currently exist, you can query a MySQL table as follows:

SELECT TRIGGER_SCHEMA, TRIGGER_NAME, EVENT_MANIPULATION, EVENT_OBJECT_TABLE, ACTION_STATEMENT, ACTION_TIMING, ACTION_REFERENCE_OLD_ROW
FROM information_schema.triggers
LIMIT 0 , 30

On my system, I got the following result:

zce before_grp_table_update UPDATE grp_table
BEGIN
INSERT INTO grp_audit_table
SET action = 'update',
rank = OLD.rank,
name = OLD.name,
score = OLD.score,
serialno = OLD.serialno,
changedon = NOW();
END
BEFORE OLD

Note: you cannot have more than one trigger type associated with a table. This means you can’t have two triggers with different names but identical EVENT and TIMING. For example, you cannot have two UPDATE BEFORE triggers associated with a table. You may have multiple triggers as long as they are of different types, i.e. UPDATE BEFORE and UPDATE AFTER triggers are permissible.

This work is licensed under a Creative Commons License
.

Advertisements

Actions

Information

2 responses

23 07 2010
John Brasher

Thanks Sharon great example.

23 07 2010
Sharon Lee Levy

John, you’re welcome; glad you like it. 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




%d bloggers like this: