Log trigger
Encyclopedia
In relational database
s, the Log trigger or History trigger is a mechanism for automatic recording of information about changes -inserting, updating 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 dimension
s.
which we want to audit. This table
contains the following columns
:
The column
These columns
are defined to have the following types:
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
than the original table
, and additionally two new columns
of type
. 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
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
of any row
is exactly the
are common to that point in time, since -by definition- the value of
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
management methodologies, The log trigger falls into the following:
IBM DB2
-- 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.HistoyTable (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.HistoyTable (Column1, Column2, ..., Columnn, StartDate, EndDate)
VALUES (N.Column1, N.Column2, ..., N.Columnn, Now, NULL);
END;
Microsoft SQL Server
CREATE TRIGGER HistoryTable ON OriginalTable FOR DELETE, INSERT, UPDATE AS
DECLARE @NOW DATETIME
SET @NOW = CURRENT_TIMESTAMP
UPDATE HistoryTable
SET EndDate = @now
FROM HistoryTable, DELETED
WHERE HistoryTable.Column1 = DELETED.Column1
AND HistoryTable.EndDate IS NULL
INSERT INTO HistoryTable (Column1, Column2, ..., Columnn, StartDate, EndDate)
SELECT (Column1, Column2, ..., Columnn, @NOW, NULL)
FROM INSERTED
MySQL
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;
Oracle
CREATE OR REPLACE TRIGGER "HistoryTable"
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;
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 an 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
used.
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 < EndDate OR EndDate IS NULL)
SELECT Column1, Column2, ..., Columnn
FROM HistoryTable
WHERE Column1 = @Key
AND @Date >= StartDate
AND (@Date < EndDate OR EndDate IS NULL)
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 H1.EndDate IS NULL
There are several options to achieve or maximize the primary key immutability:
Relational database
A relational database is a database that conforms to relational model theory. The software used in a relational database is called a relational database management system . Colloquial use of the term "relational database" may refer to the RDBMS software, or the relational database itself...
s, the Log trigger or History trigger is a mechanism for automatic recording of information about changes -inserting, updating and deleting rows
Row (database)
In the context of a relational database, a row—also called a record or tuple—represents a single, implicitly structured data item in a table. In simple terms, a database table can be thought of as consisting of rows and columns or fields...
- in a database table
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
.
It is a particular technique for change data capturing
Change data capture
In databases, change data capture is a set of software design patterns used to determine the data that has changed so that action can be taken using the changed data...
, and -in data warehousing- for dealing with slowly changing dimension
Slowly Changing Dimension
Dimension is a term in data management and data warehousing that refers to logical groupings of data such as geographical location, customer information, or product information...
s.
Definition
Suppose there is a tableTable (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
which we want to audit. This table
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
contains the following columns
Column (database)
In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....
:
Column1, Column2, ..., ColumnnThe column
Column (database)
In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....
Column1 is assumed to be the primary key.These columns
Column (database)
In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....
are defined to have the following types:
Type1, Type2, ..., TypenThe Log Trigger works writing the changes (INSERT
Insert (SQL)
An SQL INSERT statement adds one or more records to any single table in a relational database.-Basic form:Insert statements have the following form:* INSERT INTO table VALUES...
, UPDATE
Update (SQL)
An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.The UPDATE statement has the following form:...
and DELETE
Delete (SQL)
In the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:The DELETE statement follows the syntax:...
operations) on the table
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
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
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
contains the same columns
Column (database)
In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....
than the original table
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
, and additionally two new columns
Column (database)
In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....
of type
DATETIME: StartDate and EndDate. This is known as tuple versioningTuple-versioning
Tuple-versioning is a mechanism used in a relational database management system to store past states of a relation. Normally, only the current state is captured....
. These two additional columns
Column (database)
In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....
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
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
, the following structure is created in the history table
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
. Data is shown as example.
Notice that if they are shown chronologically the
EndDate columnColumn (database)
In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....
of any row
Row (database)
In the context of a relational database, a row—also called a record or tuple—represents a single, implicitly structured data item in a table. In simple terms, a database table can be thought of as consisting of rows and columns or fields...
is exactly the
StartDate of its sucesor (if any). It does not mean that both rowsRow (database)
In the context of a relational database, a row—also called a record or tuple—represents a single, implicitly structured data item in a table. In simple terms, a database table can be thought of as consisting of rows and columns or fields...
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
Compatibility notes
- The function
GetDateis used to get the system date and time, an specific RDBMSRelational database management systemA relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
could either use another function name, or get this information by another way. - Several RDBMSRelational database management systemA relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
(DB2, MySQL) do not support that the same trigger can be attached to more than one operation (INSERTInsert (SQL)An SQL INSERT statement adds one or more records to any single table in a relational database.-Basic form:Insert statements have the following form:* INSERT INTO table VALUES...
, DELETEDelete (SQL)In the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:The DELETE statement follows the syntax:...
, UPDATEUpdate (SQL)An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.The UPDATE statement has the following form:...
). In such a case a trigger must be created for each operation; For an INSERTInsert (SQL)An SQL INSERT statement adds one or more records to any single table in a relational database.-Basic form:Insert statements have the following form:* INSERT INTO table VALUES...
operation only the inserting section must be specified, for a DELETEDelete (SQL)In the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:The DELETE statement follows the syntax:...
operation only the deleting section must be specified, and for an UPDATEUpdate (SQL)An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.The UPDATE statement has the following form:...
operation both sections must be present, just as it is shown above (the deleting section first, then the inserting section), because an UPDATEUpdate (SQL)An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.The UPDATE statement has the following form:...
operation is logically represented as a DELETEDelete (SQL)In the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:The DELETE statement follows the syntax:...
operation followed by an INSERTInsert (SQL)An SQL INSERT statement adds one or more records to any single table in a relational database.-Basic form:Insert statements have the following form:* INSERT INTO table VALUES...
operation. - In the code shown, the record data structure containing the old and new values are called
OLDandNEW. On an specific RDBMSRelational database management systemA relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
they could have different names. - In the code shown, the virtual tables are called
DELETEDandINSERTED. On an specific RDBMSRelational database management systemA relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
they could have different names. Another RDBMSRelational database management systemA relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
(DB2) even let the name of these logical tables be specified. - In the code shown, comments are in C/C++ style, they could not be supported by an specific RDBMSRelational database management systemA relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
, or a different syntax should be used. - Several RDBMSRelational database management systemA relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
require that the body of the trigger is enclosed betweenBEGINandENDkeywords.
Data warehousing
According with the slowly changing dimensionSlowly Changing Dimension
Dimension is a term in data management and data warehousing that refers to logical groupings of data such as geographical location, customer information, or product information...
management methodologies, The log trigger falls into the following:
- Type 2 (tuple versioningTuple-versioningTuple-versioning is a mechanism used in a relational database management system to store past states of a relation. Normally, only the current state is captured....
variant) - Type 4 (use of history tables)
IBM DB2IBM DB2The IBM DB2 Enterprise Server Edition is a relational model database server developed by IBM. It primarily runs on Unix , Linux, IBM i , z/OS and Windows servers. DB2 also powers the different IBM InfoSphere Warehouse editions...
- A trigger cannot be attached to more than one operation (INSERTInsert (SQL)An SQL INSERT statement adds one or more records to any single table in a relational database.-Basic form:Insert statements have the following form:* INSERT INTO table VALUES...
, DELETEDelete (SQL)In the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:The DELETE statement follows the syntax:...
, UPDATEUpdate (SQL)An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.The UPDATE statement has the following form:...
), so a trigger must be created for each operation. - 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
Ofor old values andNfor 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.HistoyTable (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.HistoyTable (Column1, Column2, ..., Columnn, StartDate, EndDate)
VALUES (N.Column1, N.Column2, ..., N.Columnn, Now, NULL);
END;
Microsoft SQL ServerMicrosoft SQL ServerMicrosoft SQL Server is a relational database server, developed by Microsoft: It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network...
- The same trigger can be attached to all the INSERTInsert (SQL)An SQL INSERT statement adds one or more records to any single table in a relational database.-Basic form:Insert statements have the following form:* INSERT INTO table VALUES...
, DELETEDelete (SQL)In the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:The DELETE statement follows the syntax:...
, and UPDATEUpdate (SQL)An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.The UPDATE statement has the following form:...
operations. - Old and new values as rows of virtual tables named
DELETEDandINSERTED.
CREATE TRIGGER HistoryTable ON OriginalTable FOR DELETE, INSERT, UPDATE AS
DECLARE @NOW DATETIME
SET @NOW = CURRENT_TIMESTAMP
UPDATE HistoryTable
SET EndDate = @now
FROM HistoryTable, DELETED
WHERE HistoryTable.Column1 = DELETED.Column1
AND HistoryTable.EndDate IS NULL
INSERT INTO HistoryTable (Column1, Column2, ..., Columnn, StartDate, EndDate)
SELECT (Column1, Column2, ..., Columnn, @NOW, NULL)
FROM INSERTED
MySQLMySQLMySQL officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My...
- A trigger cannot be attached to more than one operation (INSERTInsert (SQL)An SQL INSERT statement adds one or more records to any single table in a relational database.-Basic form:Insert statements have the following form:* INSERT INTO table VALUES...
, DELETEDelete (SQL)In the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:The DELETE statement follows the syntax:...
, UPDATEUpdate (SQL)An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.The UPDATE statement has the following form:...
), so a trigger must be created for each operation. - The old and new values are exposed as fields of a record data structures called
OldandNew.
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;
OracleOracle DatabaseThe Oracle Database is an object-relational database management system produced and marketed by Oracle Corporation....
- The same trigger can be attached to all the INSERTInsert (SQL)An SQL INSERT statement adds one or more records to any single table in a relational database.-Basic form:Insert statements have the following form:* INSERT INTO table VALUES...
, DELETEDelete (SQL)In the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:The DELETE statement follows the syntax:...
, and UPDATEUpdate (SQL)An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.The UPDATE statement has the following form:...
operations. - The old and new values are exposed as fields of a record data structures called
:OLDand:NEW. - It is necessary to test the nullity of the fields of the
:NEWrecord that define the primary key (when a DELETEDelete (SQL)In the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:The DELETE statement follows the syntax:...
operation is performed), in order to avoid the insertion of a new row with null values in all columns.
CREATE OR REPLACE TRIGGER "HistoryTable"
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;
Historic information
Typically, database backupsDatabase dump
A database dump contains a record of the table structure and/or the data from a database and is usually in the form of a list of SQL statements. A database dump is most often used for backing up a database so that its contents can be restored in the event of data loss. Corrupted databases can often...
are used to store and retrieve historic information. A database backup
Database dump
A database dump contains a record of the table structure and/or the data from a database and is usually in the form of a list of SQL statements. A database dump is most often used for backing up a database so that its contents can be restored in the event of data loss. Corrupted databases can often...
is a security mechanism, more than an effective way to retrieve ready-to-use historic information.
A (full) database backup
Database dump
A database dump contains a record of the table structure and/or the data from a database and is usually in the form of a list of SQL statements. A database dump is most often used for backing up a database so that its contents can be restored in the event of data loss. Corrupted databases can often...
is only an 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
Database dump
A database dump contains a record of the table structure and/or the data from a database and is usually in the form of a list of SQL statements. A database dump is most often used for backing up a database so that its contents can be restored in the event of data loss. Corrupted databases can often...
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 RDBMSRelational database management system
A relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
used.
Advantages
- It is simple.
- It is not a commercial product, it works with available features in common RDBMSRelational database management systemA relational database management system is a database management system that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model....
. - It is automatic, once it is created, it works with no further human intervention.
- It is not required to have good knowledge about the tables of the database, or the data model.
- Changes in current programming are not required.
- Changes in the current tablesTable (database)In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
are not required, because log data of ant tableTable (database)In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
is stored in a differen tableTable (database)In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
. - It works for both programming queries and ad-hoc queries.
- Only changes (INSERTInsert (SQL)An SQL INSERT statement adds one or more records to any single table in a relational database.-Basic form:Insert statements have the following form:* INSERT INTO table VALUES...
, UPDATEUpdate (SQL)An SQL UPDATE statement changes the data of one or more records in a table. Either all the rows can be updated, or a subset may be chosen using a condition.The UPDATE statement has the following form:...
and DELETEDelete (SQL)In the database structured query language , the DELETE statement removes one or more records from a table. A subset may be defined for deletion using a condition, otherwise all records are removed.-Usage:The DELETE statement follows the syntax:...
operations) are registered, so the growing rate of the history tables are proportional to the changes. - It is not necessary apply the trigger to all the tables on database, it can be applied to certain tablesTable (database)In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
, or certain columnsColumn (database)In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table. The columns provide the structure according to which the rows are composed....
of a tableTable (database)In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
.
Disadvantages
- 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.
Getting the current version of a table
SELECT Column1, Column2, ..., Columnn
FROM HistoryTable
WHERE EndDate IS NULL
It should return the same resultset of the whole original table
Table (database)
In relational databases and flat file databases, a table is a set of data elements that is organized using a model of vertical columns and horizontal rows. A table has a specified number of columns, but can have any number of rows...
.
Getting the version of a table in a certain point of time
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)
Getting the information of an entity in a certain point of time
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)
Getting the history of an entity
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
Getting when and how an entity was created
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 H1.EndDate IS NULL
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:
- Use of a surrogate keySurrogate keyA surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data.- Definition :There are at least two definitions of a surrogate:...
as a primary key. Since there is no reason to change a value with no meaning other than identity and uniqueness, it would never change. - Use of a immutable natural keyNatural keyIn relational model database design, a natural key is a candidate key that has a logical relationship to the attributes within that row. A natural key is sometimes called a domain key.The main advantage of a natural key over a surrogate key, which has no...
as a primary key. In a good database design, a natural keyNatural keyIn relational model database design, a natural key is a candidate key that has a logical relationship to the attributes within that row. A natural key is sometimes called a domain key.The main advantage of a natural key over a surrogate key, which has no...
which can change should not be considered as a "real" primary key. - Use of a mutable natural keyNatural keyIn relational model database design, a natural key is a candidate key that has a logical relationship to the attributes within that row. A natural key is sometimes called a domain key.The main advantage of a natural key over a surrogate key, which has no...
as a primary key (it is widely discouraged) where changes are propagated in every place where it is a foreign keyForeign keyIn the context of relational databases, a foreign key is a referential constraint between two tables.A foreign key is a field in a relational table that matches a candidate key of another table...
. In such a case, the history table should be also affected.
See also
- Relational database
- Primary key
- Natural keyNatural keyIn relational model database design, a natural key is a candidate key that has a logical relationship to the attributes within that row. A natural key is sometimes called a domain key.The main advantage of a natural key over a surrogate key, which has no...
- Surrogate keySurrogate keyA surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data.- Definition :There are at least two definitions of a surrogate:...
- Change data captureChange data captureIn databases, change data capture is a set of software design patterns used to determine the data that has changed so that action can be taken using the changed data...
- Slowly changing dimensionSlowly Changing DimensionDimension is a term in data management and data warehousing that refers to logical groupings of data such as geographical location, customer information, or product information...
- Tuple versioningTuple-versioningTuple-versioning is a mechanism used in a relational database management system to store past states of a relation. Normally, only the current state is captured....