MySQL – Triggers

[adsense id=”0514458240″ width=”468″ height=”60″]

Triggers can be extremely important in keeping data consistent through the database, for auditing purposes or for massaging the data into various tables to improve performance at a later stage. In most cases this may be performed within the application directly but if someone enters directly into the database this may be bipassed resulting in inconsistencies within the data model.

I have created an example below of a trigger. An application has an internal userid integer value which is used to update a record. We can to keep an audit of the previous value. A trigger is created to handle this:

The code for the trigger is:

CREATE TRIGGER process01
BEFORE UPDATE ON process
FOR EACH ROW BEGIN
  INSERT INTO audit(id, userid) VALUES (old.id, old.userid);
END;
$$

This basically keeps track of the old values stored in the table before the UPDATE takes place. In this case the trigger may be executed either BEFORE or AFTER the UPDATE statement with the same result. However, it should be noted that if you need to update the new values to be stored then this is only permissible BEFORE UPDATE. For example:

CREATE TRIGGER process01
AFTER UPDATE ON process
FOR EACH ROW BEGIN
  INSERT INTO audit(id, userid) VALUES (old.id, old.userid);
  SET NEW.userid = 0;
END;
$$

Attempting to apply this to the database will result in:

mysql> delimiter $$
mysql> CREATE TRIGGER process01
    -> AFTER UPDATE ON process
    -> FOR EACH ROW BEGIN
    ->   INSERT INTO audit(id, userid) VALUES (old.id, old.userid);
    ->   SET NEW.userid = 0;
    -> END;
    -> $$
ERROR 1362 (HY000): Updating of NEW row is not allowed in after trigger

Now lets have a look at how this code works:

mysql> create table audit(id int, userid int);
Query OK, 0 rows affected (0.06 sec)

mysql> create table process (id int, userid int);
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter $$
mysql> create trigger process_before
    -> before update on process
    -> for each row begin
    -> insert into audit(id, userid) values (old.id, old.userid);
    -> end;
    -> $$
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> insert into process(id, userid) values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from process;
+------+--------+
| id   | userid |
+------+--------+
|    1 |      1 | 
+------+--------+
1 row in set (0.00 sec)

mysql> select * from audit;
Empty set (0.01 sec)

mysql> update process set id = 2,userid=99;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from audit;
+------+--------+
| id   | userid |
+------+--------+
|    1 |      1 | 
+------+--------+
1 row in set (0.00 sec)

mysql> update process set id = 1,userid=23;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from audit;
+------+--------+
| id   | userid |
+------+--------+
|    1 |      1 | 
|    2 |     99 | 
+------+--------+
2 rows in set (0.00 sec)