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.
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`surname` varchar(45) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE `student_audit` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(45) NOT NULL,
`surname` varchar(45) NOT NULL,
`row_id` int(10) unsigned NOT NULL,
`timestamp` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
DROP TRIGGER IF EXISTS `after_insert`;
DELIMITER $$
CREATE TRIGGER `after_insert`
AFTER INSERT ON `student`
FOR EACH ROW
BEGIN
INSERT INTO `student_audit`
(`row_id`, `name`, `surname`, `timestamp`)
VALUES
(NEW.`id`, NEW.`name`, NEW.`surname`, now());
END$$
DELIMITER ;
mysql> SELECT * FROM student;
Empty set (0.00 sec)
mysql> SELECT * FROM student_audit;
Empty set (0.01 sec)
mysql> INSERT INTO student (name, surname) VALUES ('inanzzz', 'test');
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM student;
+----+---------+---------+---------------------+
| id | name | surname | timestamp |
+----+---------+---------+---------------------+
| 1 | inanzzz | test | 2015-05-01 20:27:59 |
+----+---------+---------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM student_audit;
+----+---------+---------+--------+---------------------+
| id | name | surname | row_id | timestamp |
+----+---------+---------+--------+---------------------+
| 1 | inanzzz | test | 1 | 2015-05-01 20:27:59 |
+----+---------+---------+--------+---------------------+
1 row in set (0.00 sec)