Log trigger

Uses your system voice; voice and rate in settings.

Snapshot

≈ 6 min read

Names in other languages

No names in other languages are held for this entry yet.

Article bodies are English and are not machine-translated. A name in another language is not a translation of the article.

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.

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. 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 -- 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; 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 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; 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; CREATE OR REPLACE FUNCTION process_for_table() RETURNS TRIGGER AS $$ DECLARE now TIMESTAMP := NOW(); BEGIN --- deleting section IF (TG_OP = 'UPDATE' OR TG_OP = 'DELETE') THEN UPDATE HistoricTable SET EndDate = now FROM HistoricTable AS H INNER JOIN old_table ON H.ColumnID = old_table.ColumnID WHERE HistoricTable.ColumnID = H.ColumnID AND HistoricTable.EndDate IS NULL; END IF; --- inserting section IF (TG_OP = 'INSERT' OR TG_OP = 'UPDATE') THEN INSERT INTO HistoricTable SELECT ColumnID, Column2, ..., Columnn, now, NULL FROM new_table; END IF; RETURN NULL; END; $$ LANGUAGE plpgsql CREATE TRIGGER TriggerForTableInsert AFTER INSERT ON OriginalTable REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION process_for_table(); CREATE TRIGGER TriggerForTableUpdate AFTER UPDATE ON OriginalTable REFERENCING OLD TABLE AS old_table NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION process_for_table(); CREATE TRIGGER TriggerForTableDelete AFTER DELETE ON OriginalTable REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION process_for_table(); SELECT Column1, Column2, ..., Columnn FROM HistoryTable WHERE EndDate IS NULL It should return the same resultset of the whole original table. SELECT Column1, Column2, ..., Columnn FROM HistoryTable WHERE @Date >= StartDate AND (@Date SELECT Column1, Column2, ..., Columnn FROM HistoryTable WHERE Column1 = @Key AND @Date >= StartDate AND (@Date SELECT Column1, Column2, ..., Columnn, StartDate, EndDate FROM HistoryTable WHERE Column1 = @Key ORDER BY StartDate 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

References

Text derived from https://en.wikipedia.org/w/index.php?oldid=1334977148 under CC BY-SA 4.0; modifications have been made.

Provenance

snapshot enwiki-001334977148 · captured 1769447070.0 · version history

Cite this page as: encyclopegia.org, “Log trigger”, snapshot enwiki-001334977148. https://encyclopegia.org/a/log-trigger

Sources cited in this revision

1 named source(s) extracted from the cited revision. A source with no author is shown by its publication; nothing is invented.