inanzzz | Creating an example of after insert 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

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;

Audit table

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;

Trigger

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 ;

Test

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)