In relational databases, the log trigger or history trigger is a mechanism for automatic recording of information about changes inserting or/and updating or/and deleting rows in a database table.
It is a particular technique for change data capturing, and in data warehousing for dealing with slowly changing dimensions.
Suppose there is a table which we want to audit. This table contains the following columns:
Column1, Column2, ..., Columnn
The column Column1
is assumed to be the primary key.
These columns are defined to have the following types:
Type1, Type2, ..., Typen
The Log Trigger works writing the changes (INSERT, UPDATE and DELETE operations) on the table in another, history table, defined as following:
CREATE TABLE HistoryTable (
Column1 Type1,
Column2 Type2,
: :
Columnn Typen,
StartDate DATETIME,
EndDate DATETIME
)
As shown above, this new table contains the same columns as the original table, and additionally two new columns of type DATETIME
: StartDate
and EndDate
. This is known as tuple versioning. These two additional columns define a period of time of "validity" of the data associated with a specified entity (the entity of the primary key), or in other words, it stores how the data were in the period of time between the StartDate
(included) and EndDate
(not included).
For each entity (distinct primary key) on the original table, the following structure is created in the history table. Data is shown as example.
Notice that if they are shown chronologically the EndDate
column of any row is exactly the StartDate
of its successor (if any). It does not mean that both rows are common to that point in time, since -by definition- the value of EndDate
is not included.
There are two variants of the Log trigger, depending how the old values (DELETE, UPDATE) and new values (INSERT, UPDATE) are exposed to the trigger (it is RDBMS dependent):
Old and new values as fields of a record data structure
CREATE TRIGGER HistoryTable ON OriginalTable FOR INSERT, DELETE, UPDATE AS
DECLARE @Now DATETIME
SET @Now = GETDATE()
/* deleting section */
UPDATE HistoryTable
SET EndDate = @Now
WHERE EndDate IS NULL
AND Column1 = OLD.Column1
/* inserting section */
INSERT INTO HistoryTable (Column1, Column2, ...,Columnn, StartDate, EndDate)
VALUES (NEW.Column1, NEW.Column2, ..., NEW.Columnn, @Now, NULL)
Old and new values as rows of virtual tables
CREATE TRIGGER HistoryTable ON OriginalTable FOR INSERT, DELETE, UPDATE AS
DECLARE @Now DATETIME
SET @Now = GETDATE()
/* deleting section */
UPDATE HistoryTable
SET EndDate = @Now
FROM HistoryTable, DELETED
WHERE HistoryTable.Column1 = DELETED.Column1
AND HistoryTable.EndDate IS NULL
/* inserting section */
INSERT INTO HistoryTable
(Column1, Column2, ..., Columnn, StartDate, EndDate)
SELECT (Column1, Column2, ..., Columnn, @Now, NULL)
FROM INSERTED
GetDate()
is used to get the system date and time, a specific RDBMS could either use another function name, or get this information by another way.OLD
and NEW
. On a specific RDBMS they could have different names.DELETED
and INSERTED
. On a specific RDBMS they could have different names. Another RDBMS (Db2) even let the name of these logical tables be specified.BEGIN
and END
keywords.According with the slowly changing dimension management methodologies, The log trigger falls into the following:
O
for old values and N
for new values.-- Trigger for INSERT
CREATE TRIGGER Database.TableInsert AFTER INSERT ON Database.OriginalTable
REFERENCING NEW AS N
FOR EACH ROW MODE DB2SQL
BEGIN
DECLARE Now TIMESTAMP;
SET NOW = CURRENT TIMESTAMP;
INSERT INTO Database.HistoryTable (Column1, Column2, ..., Columnn, StartDate, EndDate)
VALUES (N.Column1, N.Column2, ..., N.Columnn, Now, NULL);
END;
-- Trigger for DELETE
CREATE TRIGGER Database.TableDelete AFTER DELETE ON Database.OriginalTable
REFERENCING OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN
DECLARE Now TIMESTAMP;
SET NOW = CURRENT TIMESTAMP;
UPDATE Database.HistoryTable
SET EndDate = Now
WHERE Column1 = O.Column1
AND EndDate IS NULL;
END;
-- Trigger for UPDATE
CREATE TRIGGER Database.TableUpdate AFTER UPDATE ON Database.OriginalTable
REFERENCING NEW AS N OLD AS O
FOR EACH ROW MODE DB2SQL
BEGIN
DECLARE Now TIMESTAMP;
SET NOW = CURRENT TIMESTAMP;
UPDATE Database.HistoryTable
SET EndDate = Now
WHERE Column1 = O.Column1
AND EndDate IS NULL;
INSERT INTO Database.HistoryTable (Column1, Column2, ..., Columnn, StartDate, EndDate)
VALUES (N.Column1, N.Column2, ..., N.Columnn, Now, NULL);
END;
DELETED
and INSERTED
.CREATE TRIGGER TableTrigger ON OriginalTable FOR DELETE, INSERT, UPDATE AS
DECLARE @NOW DATETIME
SET @NOW = CURRENT_TIMESTAMP
UPDATE HistoryTable
SET EndDate = @now
FROM HistoryTable, DELETED
WHERE HistoryTable.ColumnID = DELETED.ColumnID
AND HistoryTable.EndDate IS NULL
INSERT INTO HistoryTable (ColumnID, Column2, ..., Columnn, StartDate, EndDate)
SELECT ColumnID, Column2, ..., Columnn, @NOW, NULL
FROM INSERTED
Old
and New
.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;
:OLD
and :NEW
.:NEW
record that define the primary key (when a DELETE operation is performed), in order to avoid the insertion of a new row with null values in all columns.CREATE OR REPLACE TRIGGER TableTrigger
AFTER INSERT OR UPDATE OR DELETE ON OriginalTable
FOR EACH ROW
DECLARE Now TIMESTAMP;
BEGIN
SELECT CURRENT_TIMESTAMP INTO Now FROM Dual;
UPDATE HistoryTable
SET EndDate = Now
WHERE EndDate IS NULL
AND Column1 = :OLD.Column1;
IF :NEW.Column1 IS NOT NULL THEN
INSERT INTO HistoryTable (Column1, Column2, ..., Columnn, StartDate, EndDate)
VALUES (:NEW.Column1, :NEW.Column2, ..., :NEW.Columnn, Now, NULL);
END IF;
END;
Typically, database backups are used to store and retrieve historic information. A database backup is a security mechanism, more than an effective way to retrieve ready-to-use historic information.
A (full) database backup is only a snapshot of the data in specific points of time, so we could know the information of each snapshot, but we can know nothing between them. Information in database backups is discrete in time.
Using the log trigger the information we can know is not discrete but continuous, we can know the exact state of the information in any point of time, only limited to the granularity of time provided with the DATETIME
data type of the RDBMS used.
SELECT Column1, Column2, ..., Columnn
FROM HistoryTable
WHERE EndDate IS NULL
It should return the same resultset of the whole original table.
Suppose the @DATE
variable contains the point or time of interest.
SELECT Column1, Column2, ..., Columnn
FROM HistoryTable
WHERE @Date >= StartDate
AND (@Date < EndDate OR EndDate IS NULL)
Suppose the @DATE
variable contains the point or time of interest, and the @KEY
variable contains the primary key of the entity of interest.
SELECT Column1, Column2, ..., Columnn
FROM HistoryTable
WHERE Column1 = @Key
AND @Date >= StartDate
AND (@Date < EndDate OR EndDate IS NULL)
Suppose the @KEY
variable contains the primary key of the entity of interest.
SELECT Column1, Column2, ..., Columnn, StartDate, EndDate
FROM HistoryTable
WHERE Column1 = @Key
ORDER BY StartDate
Suppose the @KEY
variable contains the primary key of the entity of interest.
SELECT H2.Column1, H2.Column2, ..., H2.Columnn, H2.StartDate
FROM HistoryTable AS H2 LEFT OUTER JOIN HistoryTable AS H1
ON H2.Column1 = H1.Column1
AND H2.Column1 = @Key
AND H2.StartDate = H1.EndDate
WHERE H2.EndDate IS NULL
Since the trigger requires that primary key being the same throughout time, it is desirable to either ensure or maximize its immutability, if a primary key changed its value, the entity it represents would break its own history.
There are several options to achieve or maximize the primary key immutability:
Sometimes the Slowly changing dimension is used as a method, this diagram is an example: