inanzzz | MySQL ile denetim amaçlı after insert trigger yaratma

Triggerler INSERT, UPDATE ve DELETE gibi durumlarda, zamanlama olarak BEFORE ve AFTER çalışırlar. Bu şekilde bir tablo toplam olarak 6 tane trigger tutabilir.

Örnek tablo

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;

Denetim tablosu

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
    DECLARE audit_time DATETIME;
    SET audit_time = now();
    
    INSERT INTO `student_audit`
	(`row_id`, `name`, `surname`, `timestamp`)
    VALUES
	(NEW.`id`, NEW.`name`, NEW.`surname`, audit_time);
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)