mpopp.net blog – Markus Popp

Frank Mash did some testing with triggers and inspired me to try to create a log table based on triggers. This also answers his question “After playing earlier with triggers, I wanted to see whether one can update another table using MySQL triggers. I tried something like … but it didn’t work. Is it possible or am I shooting in the dark here?” with a clear yes!

First I created a data and a log table:

mysql> CREATE TABLE data_table (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> d1 VARCHAR(200) NOT NULL UNIQUE,
    -> d2 DECIMAL(9,2) NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TABLE log_table (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> id_data INT UNSIGNED NOT NULL,
    -> old_d1 VARCHAR(200) NULL,
    -> new_d1 VARCHAR(200) NULL,
    -> old_d2 DECIMAL(9,2) NULL,
    -> new_d2 DECIMAL(9,2) NULL,
    -> kind_of_change ENUM('insert', 'update', 'delete') NOT NULL,
    -> ts TIMESTAMP NOT NULL) ENGINE=InnoDB;
Query OK, 0 rows affected (0.02 sec)

Then I needed three triggers to log each kind of modification: insert, update and delete. I used after triggers to make sure that there’s only a log entry, if the modification really worked:

mysql> DELIMITER //

mysql> DROP TRIGGER test.data_table_ai//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TRIGGER test.data_table_ai
    ->   AFTER INSERT ON test.data_table
    ->   FOR EACH ROW
    -> BEGIN
    ->
    ->   INSERT INTO log_table
    ->     (id_data, old_d1, new_d1,
    ->      old_d2, new_d2,
    ->      kind_of_change, ts)
    ->     VALUES
    ->     (new.id, NULL, new.d1,
    ->      NULL, new.d2, 'insert', now());
    ->
    -> END //
Query OK, 0 rows affected (0.02 sec)


mysql> DROP TRIGGER test.data_table_au//
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TRIGGER test.data_table_au
    ->   AFTER UPDATE ON test.data_table
    ->   FOR EACH ROW
    -> BEGIN
    ->
    ->   INSERT INTO log_table
    ->     (id_data, old_d1, new_d1,
    ->      old_d2, new_d2,
    ->      kind_of_change, ts)
    ->     VALUES
    ->     (new.id, old.d1, new.d1,
    ->      old.d2, new.d2, 'update', now());
    ->
    -> END //
Query OK, 0 rows affected (0.00 sec)


mysql> DROP TRIGGER test.data_table_ad//
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE TRIGGER test.data_table_ad
    ->   AFTER DELETE ON test.data_table
    ->   FOR EACH ROW
    -> BEGIN
    ->
    ->   INSERT INTO log_table
    ->     (id_data, old_d1, new_d1,
    ->      old_d2, new_d2,
    ->      kind_of_change, ts)
    ->     VALUES
    ->     (old.id, old.d1, NULL,
    ->      old.d2, NULL, 'delete', now());
    ->
    -> END //
Query OK, 0 rows affected (0.02 sec)

mysql> DELIMITER ;

Finally, I simply inserted a few records and updated and deleted some of them:

mysql> INSERT INTO data_table (d1, d2) VALUES ('Markus', 534.12);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO data_table (d1, d2) VALUES ('Frank', 210.33);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO data_table (d1, d2) VALUES ('Charles', 1047.11);
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE data_table SET d2=492.11 WHERE d1='Markus';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> DELETE FROM data_table WHERE d1='Charles';
Query OK, 1 row affected (0.00 sec)

So here’s what the tables look like:

mysql> SELECT * FROM data_table;
+----+--------+--------+
| id | d1     | d2     |
+----+--------+--------+
|  1 | Markus | 492.11 |
|  2 | Frank  | 210.33 |
+----+--------+--------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM log_table\G
*************************** 1. row ***************************
            id: 1
       id_data: 1
        old_d1: NULL
        new_d1: Markus
        old_d2: NULL
        new_d2: 534.12
kind_of_change: insert
            ts: 2006-01-25 15:47:43
*************************** 2. row ***************************
            id: 2
       id_data: 2
        old_d1: NULL
        new_d1: Frank
        old_d2: NULL
        new_d2: 210.33
kind_of_change: insert
            ts: 2006-01-25 15:48:10
*************************** 3. row ***************************
            id: 3
       id_data: 3
        old_d1: NULL
        new_d1: Charles
        old_d2: NULL
        new_d2: 1047.11
kind_of_change: insert
            ts: 2006-01-25 15:48:27
*************************** 4. row ***************************
            id: 4
       id_data: 1
        old_d1: Markus
        new_d1: Markus
        old_d2: 534.12
        new_d2: 492.11
kind_of_change: update
            ts: 2006-01-25 15:48:49
*************************** 5. row ***************************
            id: 5
       id_data: 3
        old_d1: Charles
        new_d1: NULL
        old_d2: 1047.11
        new_d2: NULL
kind_of_change: delete
            ts: 2006-01-25 15:49:06
5 rows in set (0.00 sec)

Very useful, isn’t it ;)?