Challenging MySQL Trigger Operations Made Easy

In my The Wonderful (and not so Wonderful) Things about MySQL Triggers article, we saw how triggers can be useful in some contexts, and yet inferior to stored procedures in others.  Today’s follow-up will demonstrate how stored procedures can be used in conjunction with triggers to enhance their effectiveness.  In doing so, we will examine some of the limitations of triggers as implemented in MySQL as well as ways to circumvent them. Highlights include how to bind a trigger to multiple events and how to apply changes to the subject table from within a trigger.

MySQL Trigger Strengths and Weaknesses

Support for triggers in MySQL was first introduced in version 5.0.2.  With the latest MySQL Community Server being 5.5.27, it should come as no surprise that trigger support is less refined as those of some of the more senior RDBMS players.  As mentioned previously, a trigger can be defined to activate either before or after an INSERT, DELETE, or UPDATE statement.  Nonetheless, it is not possible to define multiple operations for a single trigger. For instance, you cannot design a trigger to fire whenever a query either updates OR inserts data into a given table. Likewise, it is not possible to define triggers on individual table columns either.

What can be combined are statements within an individual trigger.  For instance, here is a trigger that performs three separate operations on three different tables based on the value being inserted into the subject table:

delimiter |
 
CREATE TRIGGER combo_trig BEFORE INSERT ON table1
  FOR EACH ROW BEGIN
    INSERT INTO table2 SET col2 = NEW.col1;
    DELETE FROM table3 WHERE col1 = NEW.col1;
    UPDATE table4 SET col1 = col1 + 1 WHERE col1 = NEW.col1;
  END;
|
 
delimiter ;

Regarding the NEW prefix above, MySQL offers a way to compare the value that is about to be written to a table to the existing one.  Within the context of a trigger, the OLD.col_name alias refers to an existing field before it is updated or deleted, whereas NEW.col_name refers to the new value to be inserted or an existing one after it is updated.  In an INSERT operation like the one above, only the NEW value is pertinent.

Triggering on Multiple Events

The INSERT, DELETE, or UPDATE keyword in the statement above is called the trigger event. There are many reasons that DBAs would want to bind a trigger to more than one event for the same table.  If one were just learning how to write triggers, he or she might be tempted to define one as follows:

CREATE TRIGGER multi_purpose_trigger AFTER INSERT, UPDATE, DELETE ON subject_table 
 FOR EACH ROW 
 BEGIN 
   if inserting 
     INSERT INTO subject_table_audit values(xxx, insert); 
   else if updating 
     INSERT INTO subject_table_audit values(xxx, update); 
   else if deleting 
     INSERT INTO subject_table_audit values(xxx, delete); 
 END;

There are a couple of reasons that the above statement will fail.  First, the combining of trigger events (INSERT, UPDATE, DELETE) is not permitted.  The second issue is that there is no mechanism for determining the operation type from within a trigger.

The way to get around these limitations is to combine triggers with stored procedures. 

Define a trigger for each event that you want to bind to. These in turn will delegate the work to the same stored procedure, passing in whatever information is required:

DELIMITER $$
 
CREATE TRIGGER `subject_table_trigger_insert` AFTER INSERT on `subject_table`
FOR EACH ROW
BEGIN
        CALL sp_subject_table_trigger(NEW.Column, 'insert');
END
$$
 
CREATE TRIGGER `subject_table_trigger_update` AFTER UPDATE on `subject_table`
FOR EACH ROW
BEGIN
        CALL sp_subject_table_trigger(NEW.Column, 'update');
END
$$
 
CREATE TRIGGER `subject_table_trigger_delete` AFTER DELETE on `subject_table`
FOR EACH ROW
BEGIN
        CALL sp_subject_table_trigger(NEW.Column, 'delete');
END
$$
 
DELIMITER ;

The sp_subject_table_trigger stored procedure will handle the issuing of SQL statements for all the events which are associated with a trigger:

DELIMITER $$
 
CREATE PROCEDURE `sp_subject_table_trigger`(new_value VARCHAR(100), event VARCHAR(9))
BEGIN
    INSERT INTO subject_table_audit VALUES (new_value, event, Now());
    INSERT INTO table2 SET col2 = new_value;
    DELETE FROM table3 WHERE col1 = new_value;
    UPDATE table4 SET col1 = col1 + 1 WHERE col1 = new_value;
END
$$
 
DELIMITER ;

Having access to the event type is advantageous in that it can be utilized in conditional logic where the action depends on the event type:

 
DELIMITER $$
 
CREATE PROCEDURE `sp_subject_table_trigger`(new_value VARCHAR(100), event VARCHAR(9))
BEGIN
    CASE event
        WHEN 'insert' THEN INSERT INTO subject_table_audit VALUES (new_value, 'insert');
        WHEN 'update' THEN INSERT INTO subject_table_audit VALUES (new_value, 'update');
        WHEN 'delete' THEN INSERT INTO subject_table_audit VALUES (new_value, 'delete');
    END CASE;
END$$
 
DELIMITER ;
 

Applying Changes to the Subject Table via a Trigger

A very specialized use of triggers is to insert or update table data based on what’s being inserted.  Hence, the trigger is amending the very table that activated it.  This is not done too often because a stored procedure can manage this type of task quite nimbly, but in situations where the SQL statement is being generated in the application layer, or the table is so simple that a stored proc is not needed, a trigger can be useful. 

Let’s say that you have a table of words or expressions and contains only three columns:

• An ID
• The word/expression
• An word/expression type, i.e., numeric

There are a couple of caveats to be aware of:

1. You must use BEFORE INSERT ON and not AFTER INSERT ON.
2. Do not use an UPDATE statement.  Instead, use SET NEW.attribute without the operation type as in SET column = value;.

Here’s a sample trigger that illustrates the points made above.  It tests for a numeric value in the word field and sets num_only a Boolean – represented by the tinyint(1) type - accordingly.

The words table:

CREATE TABLE words(id       INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                   word     varchar(10), 
                   is_num   TINYINT(1) NULL DEFAULT false);

The trigger statement:

DELIMITER $$
 
 CREATE TRIGGER number_test_trig BEFORE INSERT ON words
 FOR EACH ROW
 BEGIN
   -- test whether or not value contains numbers only
   IF NEW.word REGEXP '^[0-9]+$' THEN
     SET NEW.is_num=TRUE;
   END IF;
 END;
 $$
DELIMITER ;
 
 

For you optimizers out there, the IF is redundant because the REGEXP test returns a Boolean anyway.  Therefore the column can be set directly to the REGEXP result:

  -- test whether or not value contains numbers only
  SET NEW.is_num = (NEW.word REGEXP '^[0-9]+$');

Let’s test the trigger by inserting a couple of rows into our table, one numeric, the other not:

INSERT INTO words (id, word) VALUES ("94893");
INSERT INTO words (id, word) VALUES ("not num.");

A quick SELECT * from the table confirms that the trigger is working as expected:

SELECT * FROM words;

For more information on MySQL triggers, be sure to check out the official docs on the MySQL Developer site.

See all articles by Rob Gravelle