19/04/2013 - MYSQL
Triggers are activated BEFORE
or AFTER
for INSERT
, UPDATE
and DELETE
events so we can have all together 6 triggers set for a 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;
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;
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 ;
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)