Log trigger
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. Definition. Suppose there is a table which we want to audit. This table contains the following columns: codice_1 The column codice_2 is assumed to be the primary key. These columns are defined to have the following types: codice_3 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 ( As shown above, this new table contains the same columns as the original table, and additionally two new columns of type codice_4: codice_5 and codice_6. 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 codice_5 (included) and codice_6 (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 codice_6 column of any row is exactly the codice_5 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 codice_6 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 /* 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 /* inserting section */ INSERT INTO HistoryTable SELECT (Column1, Column2, ..., Columnn, @Now, NULL) Data warehousing. According with the slowly changing dimension management methodologies, The log trigger falls into the following: -- Trigger  for INSERT Implementation in common RDBMS. IBM Db2. CREATE TRIGGER Database.TableInsert AFTER INSERT ON Database.OriginalTable REFERENCING NEW AS N FOR EACH ROW MODE DB2SQL BEGIN END; -- Trigger for DELETE CREATE TRIGGER Database.TableDelete AFTER DELETE ON Database.OriginalTable REFERENCING OLD AS O FOR EACH ROW MODE DB2SQL BEGIN 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 END; CREATE TRIGGER TableTrigger ON OriginalTable FOR DELETE, INSERT, UPDATE AS Microsoft SQL Server. DECLARE @NOW DATETIME SET @NOW = CURRENT_TIMESTAMP UPDATE HistoryTable INSERT  INTO HistoryTable (ColumnID, Column2, ..., Columnn, StartDate, EndDate) SELECT ColumnID, Column2, ..., Columnn, @NOW, NULL DELIMITER $$ MySQL. /* Trigger for INSERT */ CREATE TRIGGER HistoryTableInsert AFTER INSERT ON OriginalTable FOR EACH ROW BEGIN END; /* Trigger for DELETE */ CREATE TRIGGER HistoryTableDelete AFTER DELETE ON OriginalTable FOR EACH ROW BEGIN END; /* Trigger for UPDATE */ CREATE TRIGGER HistoryTableUpdate AFTER UPDATE ON OriginalTable FOR EACH ROW BEGIN END; CREATE OR REPLACE TRIGGER TableTrigger Oracle. AFTER INSERT OR UPDATE OR DELETE ON OriginalTable FOR EACH ROW DECLARE Now TIMESTAMP; BEGIN END; Historic information. 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 codice_4 data type of the RDBMS  used. Examples of use. Getting the current version of a table. SELECT Column1, Column2, ..., Columnn It should return the same resultset of the whole original table. Getting the version of a table in a certain point of time. Suppose the codice_29 variable contains the point or time of interest. SELECT Column1, Column2, ..., Columnn Getting the information of an entity in a certain point of time. Suppose the codice_29 variable contains the point or time of interest, and the codice_31 variable contains the primary key of the entity of interest. SELECT Column1, Column2, ..., Columnn Getting the history of an entity. Suppose  the codice_31 variable contains the primary key of the entity of interest. SELECT Column1, Column2, ..., Columnn, StartDate, EndDate Getting when and how an entity was created. Suppose the codice_31 variable contains the primary key of the entity of interest. SELECT H2.Column1, H2.Column2, ..., H2.Columnn, H2.StartDate Immutability of primary keys. 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: Alternatives. Sometimes  the Slowly changing dimension is used as a method, this diagram is an example: Notes. The Log trigger was written by Laurence R. Ugalde to automatically generate history of transactional databases.