Log trigger

<h2 id="definition">Definition</h2>
<p>Suppose there is a <a href="/facts/Table_(database)/5INvK1Vd">table</a> which we want to audit. This <a href="/facts/Table_(database)/5INvK1Vd">table</a> contains the following <a href="/facts/Column_(database)/WB91xSVs">columns</a>:
</p><p>Column1, Column2, ..., Columnn
</p><p>The <a href="/facts/Column_(database)/WB91xSVs">column</a> Column1 is assumed to be the <a href="/facts/Primary_key/veT9mAbo">primary key</a>.
</p><p>These <a href="/facts/Column_(database)/WB91xSVs">columns</a> are defined to have the following types:
</p><p>Type1, Type2, ..., Typen
</p><p>The Log Trigger works writing the changes (<a href="/facts/Insert_(SQL)/UvooNUmg">INSERT</a>, <a href="/facts/Update_(SQL)/IQz2IKpz">UPDATE</a> and <a href="/facts/Delete_(SQL)/gyapLh6T">DELETE</a> operations) on the <a href="/facts/Table_(database)/5INvK1Vd">table</a> in another, history table, defined as following:
</p>
CREATE TABLE HistoryTable (
   Column1   Type1,
   Column2   Type2,
      :        :
   Columnn   Typen,

StartDate DATETIME,
   EndDate   DATETIME
)

<p>As shown above, this new <a href="/facts/Table_(database)/5INvK1Vd">table</a> contains the same <a href="/facts/Column_(database)/WB91xSVs">columns</a> as the original <a href="/facts/Table_(database)/5INvK1Vd">table</a>, and additionally two new <a href="/facts/Column_(database)/WB91xSVs">columns</a> of type DATETIME: StartDate and EndDate. This is known as <a href="/facts/Tuple-versioning/uwF32kg8">tuple versioning</a>. These two additional <a href="/facts/Column_(database)/WB91xSVs">columns</a> define a period of time of "validity" of the data associated with a specified entity (the entity of the <a href="/facts/Primary_key/veT9mAbo">primary key</a>), or in other words, it stores how the data were in the period of time between the StartDate (included) and EndDate (not included).
</p><p>For each entity (distinct <a href="/facts/Primary_key/veT9mAbo">primary key</a>) on the original <a href="/facts/Table_(database)/5INvK1Vd">table</a>, the following structure is created in the history <a href="/facts/Table_(database)/5INvK1Vd">table</a>. Data is shown as example.
</p>

<p>Notice that if they are shown chronologically the EndDate <a href="/facts/Column_(database)/WB91xSVs">column</a> of any <a href="/facts/Row_(database)/PIpv7c9e">row</a> is exactly the StartDate of its successor (if any). It does not mean that both <a href="/facts/Row_(database)/PIpv7c9e">rows</a> are common to that point in time, since -by definition- the value of EndDate is not included.
</p><p>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):
</p><p>Old and new values as fields of a record data structure
</p>
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)

<p>Old and new values as rows of virtual tables
</p>
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

<h3>Compatibility notes</h3>
<ul><li>The function GetDate() is used to get the system date and time, a specific <a href="/facts/Relational_database_management_system/pEKGmJLy">RDBMS</a> could either use another function name, or get this information by another way.</li>
<li>Several <a href="/facts/Relational_database_management_system/pEKGmJLy">RDBMS</a> (Db2, MySQL) do not support that the same trigger can be attached to more than one operation (<a href="/facts/Insert_(SQL)/UvooNUmg">INSERT</a>, <a href="/facts/Delete_(SQL)/gyapLh6T">DELETE</a>, <a href="/facts/Update_(SQL)/IQz2IKpz">UPDATE</a>). In such a case a trigger must be created for each operation; For an <a href="/facts/Insert_(SQL)/UvooNUmg">INSERT</a> operation only the <i>inserting section</i> must be specified, for a <a href="/facts/Delete_(SQL)/gyapLh6T">DELETE</a> operation only the <i>deleting section</i> must be specified, and for an <a href="/facts/Update_(SQL)/IQz2IKpz">UPDATE</a> operation both sections must be present, just as it is shown above (the <i>deleting section</i> first, then the <i>inserting section</i>), because an <a href="/facts/Update_(SQL)/IQz2IKpz">UPDATE</a> operation is logically represented as a <a href="/facts/Delete_(SQL)/gyapLh6T">DELETE</a> operation followed by an <a href="/facts/Insert_(SQL)/UvooNUmg">INSERT</a> operation.</li>
<li>In the code shown, the record data structure containing the old and new values are called OLD and NEW. On a specific <a href="/facts/Relational_database_management_system/pEKGmJLy">RDBMS</a> they could have different names.</li>
<li>In the code shown, the virtual tables are called DELETED and INSERTED. On a specific <a href="/facts/Relational_database_management_system/pEKGmJLy">RDBMS</a> they could have different names. Another <a href="/facts/Relational_database_management_system/pEKGmJLy">RDBMS</a> (Db2) even let the name of these logical tables be specified.</li>
<li>In the code shown, comments are in C/C++ style, they could not be supported by a specific <a href="/facts/Relational_database_management_system/pEKGmJLy">RDBMS</a>, or a different syntax should be used.</li>
<li>Several <a href="/facts/Relational_database_management_system/pEKGmJLy">RDBMS</a> require that the body of the trigger is enclosed between BEGIN and END keywords.</li></ul>
<h3><a href="/facts/Data_warehousing/we5rblq9">Data warehousing</a></h3>
<p>According with the <a href="/facts/Slowly_changing_dimension/XfWHE7R1">slowly changing dimension</a> management methodologies, The log trigger falls into the following:
</p>
<ul><li><a href="/facts/Slowly_changing_dimension/XfWHE7R1">Type 2</a> (<a href="/facts/Tuple-versioning/uwF32kg8">tuple versioning</a> variant)</li>
<li><a href="/facts/Slowly_changing_dimension/XfWHE7R1">Type 4</a> (use of history tables)</li></ul>
<h2 id="implementation-in-common-rdbms">Implementation in common <a href="/facts/RDBMS/pEKGmJLy">RDBMS</a></h2>
<h3><a href="/facts/IBM_Db2/2cYITuuY">IBM Db2</a></h3>
<p>Source:<a class="footnote-ref" id="fnref:1" href="#fn:1"><sup>1</sup></a>
</p>
<ul><li>A trigger cannot be attached to more than one operation (<a href="/facts/Insert_(SQL)/UvooNUmg">INSERT</a>, <a href="/facts/Delete_(SQL)/gyapLh6T">DELETE</a>, <a href="/facts/Update_(SQL)/IQz2IKpz">UPDATE</a>), so a trigger must be created for each operation.</li>
<li>The old and new values are exposed as fields of a record data structures. The names of these records can be defined, in this example they are named as O for old values and N for new values.</li></ul>
-- 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;

<h3><a href="/facts/Microsoft_SQL_Server/eCdt2JBL">Microsoft SQL Server</a></h3>
<p>Source:<a class="footnote-ref" id="fnref:2" href="#fn:2"><sup>2</sup></a>
</p>
<ul><li>The same trigger can be attached to all the <a href="/facts/Insert_(SQL)/UvooNUmg">INSERT</a>, <a href="/facts/Delete_(SQL)/gyapLh6T">DELETE</a>, and <a href="/facts/Update_(SQL)/IQz2IKpz">UPDATE</a> operations.</li>
<li>Old and new values as rows of virtual tables named DELETED and INSERTED.</li></ul>
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

<h3><a href="/facts/MySQL/QEEDNBDP">MySQL</a></h3>
<ul><li>A trigger cannot be attached to more than one operation (<a href="/facts/Insert_(SQL)/UvooNUmg">INSERT</a>, <a href="/facts/Delete_(SQL)/gyapLh6T">DELETE</a>, <a href="/facts/Update_(SQL)/IQz2IKpz">UPDATE</a>), so a trigger must be created for each operation.</li>
<li>The old and new values are exposed as fields of a record data structures called Old and New.</li></ul>
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;

<h3><a href="/facts/Oracle_Database/FHv5g732">Oracle</a></h3>
<ul><li>The same trigger can be attached to all the <a href="/facts/Insert_(SQL)/UvooNUmg">INSERT</a>, <a href="/facts/Delete_(SQL)/gyapLh6T">DELETE</a>, and <a href="/facts/Update_(SQL)/IQz2IKpz">UPDATE</a> operations.</li>
<li>The old and new values are exposed as fields of a record data structures called :OLD and :NEW.</li>
<li>It is necessary to test the nullity of the fields of the :NEW record that define the <a href="/facts/Primary_key/veT9mAbo">primary key</a> (when a <a href="/facts/Delete_(SQL)/gyapLh6T">DELETE</a> operation is performed), in order to avoid the insertion of a new row with null values in all columns.</li></ul>
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;

<h2 id="historic-information">Historic information</h2>
<p>Typically, <a href="/facts/Database_dump/v8q8btkA">database backups</a> are used to store and retrieve historic information. A <a href="/facts/Database_dump/v8q8btkA">database backup</a> is a security mechanism, more than an effective way to retrieve ready-to-use historic information.
</p><p>A (full) <a href="/facts/Database_dump/v8q8btkA">database backup</a> 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 <a href="/facts/Database_dump/v8q8btkA">database backups</a> is discrete in time.
</p><p>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 <a href="/facts/Relational_database_management_system/pEKGmJLy">RDBMS</a> used.
</p>
<h2 id="advantages">Advantages</h2>
<ul><li>It is simple.</li>
<li>It is not a commercial product, it works with available features in common <a href="/facts/Relational_database_management_system/pEKGmJLy">RDBMS</a>.</li>
<li>It is automatic, once it is created, it works with no further human intervention.</li>
<li>It is not required to have good knowledge about the tables of the database, or the data model.</li>
<li>Changes in current programming are not required.</li>
<li>Changes in the current <a href="/facts/Table_(database)/5INvK1Vd">tables</a> are not required, because log data of any <a href="/facts/Table_(database)/5INvK1Vd">table</a> is stored in a different one.</li>
<li>It works for both programmed and ad hoc statements.</li>
<li>Only changes (<a href="/facts/Insert_(SQL)/UvooNUmg">INSERT</a>, <a href="/facts/Update_(SQL)/IQz2IKpz">UPDATE</a> and <a href="/facts/Delete_(SQL)/gyapLh6T">DELETE</a> operations) are registered, so the growing rate of the history tables are proportional to the changes.</li>
<li>It is not necessary to apply the trigger to all the tables on database, it can be applied to certain <a href="/facts/Table_(database)/5INvK1Vd">tables</a>, or certain <a href="/facts/Column_(database)/WB91xSVs">columns</a> of a <a href="/facts/Table_(database)/5INvK1Vd">table</a>.</li></ul>
<h2 id="disadvantages">Disadvantages</h2>
<ul><li>It does not automatically store information about the user producing the changes (information system user, not database user). This information might be provided explicitly. It could be enforced in information systems, but not in ad hoc queries.</li></ul>
<h2 id="examples-of-use">Examples of use</h2>
<h3>Getting the current version of a table</h3>
SELECT Column1, Column2, ..., Columnn
  FROM HistoryTable
 WHERE EndDate IS NULL

<p>It should return the same resultset of the whole original <a href="/facts/Table_(database)/5INvK1Vd">table</a>.
</p>
<h3>Getting the version of a table in a certain point of time</h3>
<p>Suppose the @DATE variable contains the point or time of interest.
</p>
SELECT  Column1, Column2, ..., Columnn
  FROM  HistoryTable
 WHERE  @Date >= StartDate
   AND (@Date < EndDate OR EndDate IS NULL)

<h3>Getting the information of an entity in a certain point of time</h3>
<p>Suppose the @DATE variable contains the point or time of interest, and the @KEY variable contains the <a href="/facts/Primary_key/veT9mAbo">primary key</a> of the entity of interest.
</p>
SELECT  Column1, Column2, ..., Columnn
  FROM  HistoryTable
 WHERE  Column1 = @Key
   AND  @Date >= StartDate
   AND (@Date <  EndDate OR EndDate IS NULL)

<h3>Getting the history of an entity</h3>
<p>Suppose the @KEY variable contains the <a href="/facts/Primary_key/veT9mAbo">primary key</a> of the entity of interest.
</p>
SELECT Column1, Column2, ..., Columnn, StartDate, EndDate
  FROM HistoryTable
 WHERE Column1 = @Key
 ORDER BY StartDate

<h3>Getting when and how an entity was created</h3>
<p>Suppose the @KEY variable contains the <a href="/facts/Primary_key/veT9mAbo">primary key</a> of the entity of interest.
</p>
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

<h2 id="immutability-of-primary-keys">Immutability of <a href="/facts/Primary_key/veT9mAbo">primary keys</a></h2>
<p>Since the trigger requires that <a href="/facts/Primary_key/veT9mAbo">primary key</a> being the same throughout time, it is desirable to either ensure or maximize its immutability, if a <a href="/facts/Primary_key/veT9mAbo">primary key</a> changed its value, the entity it represents would break its own history.
</p><p>There are several options to achieve or maximize the <a href="/facts/Primary_key/veT9mAbo">primary key</a> immutability:
</p>
<ul><li>Use of a <a href="/facts/Surrogate_Key/rJu9oHHX">surrogate key</a> as a <a href="/facts/Primary_key/veT9mAbo">primary key</a>. Since there is no reason to change a value with no meaning other than identity and uniqueness, it would never change.</li>
<li>Use of an immutable <a href="/facts/Natural_key/sCNXUylr">natural key</a> as a <a href="/facts/Primary_key/veT9mAbo">primary key</a>. In a good database design, a <a href="/facts/Natural_key/sCNXUylr">natural key</a> which can change should not be considered as a "real" <a href="/facts/Primary_key/veT9mAbo">primary key</a>.</li>
<li>Use of a mutable <a href="/facts/Natural_key/sCNXUylr">natural key</a> as a <a href="/facts/Primary_key/veT9mAbo">primary key</a> (it is widely discouraged) where changes are propagated in every place where it is a <a href="/facts/Foreign_key/52Ns8RTD">foreign key</a>. In such a case, the history table should be also affected.</li></ul>
<h3>Alternatives</h3>
<p>Sometimes the <a href="/facts/Slowly_changing_dimension/XfWHE7R1">Slowly changing dimension</a> is used as a method, this diagram is an example:
</p>

<h2 id="see-also">See also</h2>
<ul><li><a href="/facts/RDBMS/pEKGmJLy">Relational database</a></li>
<li><a href="/facts/Primary_key/veT9mAbo">Primary key</a></li>
<li><a href="/facts/Natural_key/sCNXUylr">Natural key</a></li>
<li><a href="/facts/Surrogate_key/rJu9oHHX">Surrogate key</a></li>
<li><a href="/facts/Change_data_capture/WkP5WXgf">Change data capture</a></li>
<li><a href="/facts/Slowly_changing_dimension/XfWHE7R1">Slowly changing dimension</a></li>
<li><a href="/facts/Tuple-versioning/uwF32kg8">Tuple versioning</a></li></ul>
<h2 id="notes">Notes</h2>
<p>The Log trigger was written by Laurence R. Ugalde<a class="footnote-ref" id="fnref:3" href="#fn:3"><sup>3</sup></a> to automatically generate history of transactional databases.
</p>
<h2 id="external-links">External links</h2>
<p><a href="https://github.com/laurence-ugalde/log-trigger">Log trigger</a> on <a href="/facts/GitHub/n1hNCsc3">GitHub</a>
</p>

<h2 id="references">References</h2>

<ol>
<li id="fn:1"><p>"Database Fundamentals" by Nareej Sharma et al. (First Edition, Copyright IBM Corp. 2010) <a href="#fnref:1" class="footnote-back-ref">↩</a></p></li>
<li id="fn:2"><p>"Microsoft SQL Server 2008 - Database Development" by Thobias Thernström et al. (Microsoft Press, 2009) <a href="#fnref:2" class="footnote-back-ref">↩</a></p></li>
<li id="fn:3"><p>"R. Ugalde, Laurence; Log trigger". GitHub. Retrieved 2022-06-26. <a href="https://github.com/laurence-ugalde/log-trigger" target="_blank">https://github.com/laurence-ugalde/log-trigger</a> <a href="#fnref:3" class="footnote-back-ref">↩</a></p></li>
</ol>

Log trigger open-in-new

Log trigger