inanzzz | Creating an example of after update trigger in MySQL for auditing purposes

Triggers are activated BEFORE or AFTER for INSERT, UPDATE and DELETE events so we can have all together 6 triggers set for a table.

Dummy table

Field timestamp will help us controlling triggers. It has no other purpose. Timestamp is set to change after insert and update.

CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) NOT NULL,
  `surname` varchar(45) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Audit table

CREATE TABLE `student_audit` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `row_id` int(10) unsigned NOT NULL,
  `name_old` varchar(45) NOT NULL,
  `surname_old` varchar(45) NOT NULL,
  `name_new` varchar(45) NOT NULL,
  `surname_new` varchar(45) NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Trigger

DROP TRIGGER IF EXISTS `after_update`;

DELIMITER $$

CREATE TRIGGER `after_update`
AFTER UPDATE ON `student`
FOR EACH ROW
BEGIN
    IF NEW.`timestamp` <> OLD.`timestamp` THEN
        INSERT INTO `student_audit`
            (`row_id`, `name_old`, `surname_old`, `name_new`, `surname_new`, `timestamp`)
        VALUES
            (OLD.`id`, OLD.`name`, OLD.`surname`, NEW.`name`, NEW.`surname`, now());
    END IF;
END$$

DELIMITER ;

Test

mysql> SELECT * FROM student;
Empty set (0.01 sec)

mysql> SELECT * FROM student_audit;
Empty set (0.00 sec)

mysql> INSERT INTO student (name, surname) VALUES ('inanzzz', 'test'), ('hello', 'world');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM student;
+----+---------+---------+---------------------+
| id | name    | surname | timestamp           |
+----+---------+---------+---------------------+
|  1 | inanzzz | test    | 2015-05-01 21:41:17 |
|  2 | hello   | world   | 2015-05-01 21:41:17 |
+----+---------+---------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM student_audit;
Empty set (0.00 sec)

mysql> UPDATE student SET name = 'hello' WHERE surname = 'world';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> SELECT * FROM student;
+----+---------+---------+---------------------+
| id | name    | surname | timestamp           |
+----+---------+---------+---------------------+
|  1 | inanzzz | test    | 2015-05-01 21:41:17 |
|  2 | hello   | world   | 2015-05-01 21:41:17 |
+----+---------+---------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM student_audit;
Empty set (0.00 sec)

mysql> UPDATE student SET name = 'bye' WHERE surname = 'world';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM student;
+----+---------+---------+---------------------+
| id | name    | surname | timestamp           |
+----+---------+---------+---------------------+
|  1 | inanzzz | test    | 2015-05-01 21:41:17 |
|  2 | bye     | world   | 2015-05-01 21:42:35 |
+----+---------+---------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM student_audit;
+----+--------+----------+-------------+----------+-------------+---------------------+
| id | row_id | name_old | surname_old | name_new | surname_new | timestamp           |
+----+--------+----------+-------------+----------+-------------+---------------------+
|  1 |      2 | hello    | world       | bye      | world       | 2015-05-01 21:42:35 |
+----+--------+----------+-------------+----------+-------------+---------------------+
1 row in set (0.00 sec)