-
Notifications
You must be signed in to change notification settings - Fork 0
/
Log trigger for MySQL.sql
35 lines (28 loc) · 991 Bytes
/
Log trigger for MySQL.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
DELIMITER $$
/* Trigger for INSERT */
CREATE TRIGGER HistoryTableInsert AFTER INSERT ON OriginalTable FOR EACH ROW BEGIN
DECLARE N DATETIME;
SET N = now();
INSERT INTO HistoryTable (Column1, Column2, ..., Columnn, StartDate, EndDate)
VALUES (New.Column1, New.Column2, ..., New.Columnn, N, NULL);
END;
/* Trigger for DELETE */
CREATE TRIGGER HistoryTableDelete AFTER DELETE ON OriginalTable FOR EACH ROW BEGIN
DECLARE N DATETIME;
SET N = now();
UPDATE HistoryTable
SET EndDate = N
WHERE Column1 = OLD.Column1
AND EndDate IS NULL;
END;
/* Trigger for UPDATE */
CREATE TRIGGER HistoryTableUpdate AFTER UPDATE ON OriginalTable FOR EACH ROW BEGIN
DECLARE N DATETIME;
SET N = now();
UPDATE HistoryTable
SET EndDate = N
WHERE Column1 = OLD.Column1
AND EndDate IS NULL;
INSERT INTO HistoryTable (Column1, Column2, ..., Columnn, StartDate, EndDate)
VALUES (New.Column1, New.Column2, ..., New.Columnn, N, NULL);
END;