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 table


Alan timestamp bize triggerleri kontrol etmek için yardımcı olacak. Ondan başka hiçbir amacı yok. Timestamp alanı insert ve update işlemlerinde değişir.


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;

Denetim tablosu


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)