In
relational database
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured for ...
s, 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
In a database, a table is a collection of related data organized in table format; consisting of columns and rows.
In relational databases, and flat file databases, a ''table'' is a set of data elements (values) using a model of vertical colum ...
.
It is a particular technique for
change data capturing, and in
data warehousing
In computing, a data warehouse (DW or DWH), also known as an enterprise data warehouse (EDW), is a system used for Business intelligence, reporting and data analysis and is a core component of business intelligence. Data warehouses are central Re ...
for dealing with
slowly changing dimension
In data management and data warehousing, a slowly changing dimension (SCD) is a dimension that stores data which, while generally stable, may change over time, often in an unpredictable manner. This contrasts with a rapidly changing dimension, su ...
s.
Definition
Suppose there is a
table
Table may refer to:
* Table (database), how the table data arrangement is used within the databases
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (information), a data arrangement with rows and column ...
which we want to audit. This
table
Table may refer to:
* Table (database), how the table data arrangement is used within the databases
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (information), a data arrangement with rows and column ...
contains the following
columns
A column or pillar in architecture and structural engineering is a structural element that transmits, through compression, the weight of the structure above to other structural elements below. In other words, a column is a compression member ...
:
Column1, Column2, ..., Columnn
The
column
A column or pillar in architecture and structural engineering is a structural element that transmits, through compression, the weight of the structure above to other structural elements below. In other words, a column is a compression member ...
Column1
is assumed to be the
primary key
In the relational model of databases, a primary key is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table. The database creator can choose an existing unique attribute or combinati ...
.
These
columns
A column or pillar in architecture and structural engineering is a structural element that transmits, through compression, the weight of the structure above to other structural elements below. In other words, a column is a compression member ...
are defined to have the following types:
Type1, Type2, ..., Typen
The Log Trigger works writing the changes (
INSERT
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:
The number of columns and values must be the same. If a column is not specified, the default va ...
,
UPDATE and
DELETE operations) on the
table
Table may refer to:
* Table (database), how the table data arrangement is used within the databases
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (information), a data arrangement with rows and column ...
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 may refer to:
* Table (database), how the table data arrangement is used within the databases
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (information), a data arrangement with rows and column ...
contains the same
columns
A column or pillar in architecture and structural engineering is a structural element that transmits, through compression, the weight of the structure above to other structural elements below. In other words, a column is a compression member ...
as the original
table
Table may refer to:
* Table (database), how the table data arrangement is used within the databases
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (information), a data arrangement with rows and column ...
, and additionally two new
columns
A column or pillar in architecture and structural engineering is a structural element that transmits, through compression, the weight of the structure above to other structural elements below. In other words, a column is a compression member ...
of type
DATETIME
:
StartDate
and
EndDate
. This is known as
tuple versioning. These two additional
columns
A column or pillar in architecture and structural engineering is a structural element that transmits, through compression, the weight of the structure above to other structural elements below. In other words, a column is a compression member ...
define a period of time of "validity" of the data associated with a specified entity (the entity of the
primary key
In the relational model of databases, a primary key is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table. The database creator can choose an existing unique attribute or combinati ...
), 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
In the relational model of databases, a primary key is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table. The database creator can choose an existing unique attribute or combinati ...
) on the original
table
Table may refer to:
* Table (database), how the table data arrangement is used within the databases
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (information), a data arrangement with rows and column ...
, the following structure is created in the history
table
Table may refer to:
* Table (database), how the table data arrangement is used within the databases
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (information), a data arrangement with rows and column ...
. Data is shown as example.
Notice that if they are shown chronologically the
EndDate
column
A column or pillar in architecture and structural engineering is a structural element that transmits, through compression, the weight of the structure above to other structural elements below. In other words, a column is a compression member ...
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
Compatibility notes
* The function
GetDate()
is used to get the system date and time, a specific
RDBMS
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured forma ...
could either use another function name, or get this information by another way.
* Several
RDBMS
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured forma ...
(Db2, MySQL) do not support that the same trigger can be attached to more than one operation (
INSERT
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:
The number of columns and values must be the same. If a column is not specified, the default va ...
,
DELETE,
UPDATE). In such a case a trigger must be created for each operation; For an
INSERT
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:
The number of columns and values must be the same. If a column is not specified, the default va ...
operation only the ''inserting section'' must be specified, for a
DELETE operation only the ''deleting section'' must be specified, and for an
UPDATE operation both sections must be present, just as it is shown above (the ''deleting section'' first, then the ''inserting section''), because an
UPDATE operation is logically represented as a
DELETE operation followed by an
INSERT
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:
The number of columns and values must be the same. If a column is not specified, the default va ...
operation.
* In the code shown, the record data structure containing the old and new values are called
OLD
and
NEW
. On a specific
RDBMS
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured forma ...
they could have different names.
* In the code shown, the virtual tables are called
DELETED
and
INSERTED
. On a specific
RDBMS
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured forma ...
they could have different names. Another
RDBMS
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured forma ...
(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 a specific
RDBMS
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured forma ...
, or a different syntax should be used.
* Several
RDBMS
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured forma ...
require that the body of the trigger is enclosed between
BEGIN
and
END
keywords.
Data warehousing
In computing, a data warehouse (DW or DWH), also known as an enterprise data warehouse (EDW), is a system used for Business intelligence, reporting and data analysis and is a core component of business intelligence. Data warehouses are central Re ...
According with the
slowly changing dimension
In data management and data warehousing, a slowly changing dimension (SCD) is a dimension that stores data which, while generally stable, may change over time, often in an unpredictable manner. This contrasts with a rapidly changing dimension, su ...
management methodologies, The log trigger falls into the following:
*
Type 2 (
tuple versioning variant)
*
Type 4 (use of history tables)
Implementation in common
RDBMS
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured forma ...
IBM Db2
Source:
* A trigger cannot be attached to more than one operation (
INSERT
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:
The number of columns and values must be the same. If a column is not specified, the default va ...
,
DELETE,
UPDATE), 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
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;
Microsoft SQL Server
Microsoft SQL Server is a proprietary relational database management system developed by Microsoft using Structured Query Language (SQL, often pronounced "sequel"). As a database server, it is a software product with the primary function of ...
Source:
["Microsoft SQL Server 2008 - Database Development" by Thobias Thernström et al. (Microsoft Press, 2009)]
* The same trigger can be attached to all the
INSERT
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:
The number of columns and values must be the same. If a column is not specified, the default va ...
,
DELETE, and
UPDATE operations.
* Old and new values as rows of virtual tables named
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
MySQL
MySQL () is an Open-source software, open-source relational database management system (RDBMS). Its name is a combination of "My", the name of co-founder Michael Widenius's daughter My, and "SQL", the acronym for Structured Query Language. A rel ...
* A trigger cannot be attached to more than one operation (
INSERT
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:
The number of columns and values must be the same. If a column is not specified, the default va ...
,
DELETE,
UPDATE), so a trigger must be created for each operation.
* The old and new values are exposed as fields of a record data structures called
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;
Oracle
An oracle is a person or thing considered to provide insight, wise counsel or prophetic predictions, most notably including precognition of the future, inspired by deities. If done through occultic means, it is a form of divination.
Descript ...
* The same trigger can be attached to all the
INSERT
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:
The number of columns and values must be the same. If a column is not specified, the default va ...
,
DELETE, and
UPDATE operations.
* The old and new values are exposed as fields of a record data structures called
:OLD
and
:NEW
.
* It is necessary to test the nullity of the fields of the
:NEW
record that define the
primary key
In the relational model of databases, a primary key is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table. The database creator can choose an existing unique attribute or combinati ...
(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;
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
DATETIME
data type of the
RDBMS
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured forma ...
used.
Advantages
* It is simple.
* It is not a commercial product, it works with available features in common
RDBMS
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured forma ...
.
* 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
tables are not required, because log data of any
table
Table may refer to:
* Table (database), how the table data arrangement is used within the databases
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (information), a data arrangement with rows and column ...
is stored in a different one.
* It works for both programmed and ad hoc statements.
* Only changes (
INSERT
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:
The number of columns and values must be the same. If a column is not specified, the default va ...
,
UPDATE and
DELETE operations) are registered, so the growing rate of the history tables are proportional to the changes.
* It is not necessary to apply the trigger to all the tables on database, it can be applied to certain
tables, or certain
columns
A column or pillar in architecture and structural engineering is a structural element that transmits, through compression, the weight of the structure above to other structural elements below. In other words, a column is a compression member ...
of a
table
Table may refer to:
* Table (database), how the table data arrangement is used within the databases
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (information), a data arrangement with rows and column ...
.
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.
Examples of use
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 may refer to:
* Table (database), how the table data arrangement is used within the databases
* Table (furniture), a piece of furniture with a flat surface and one or more legs
* Table (information), a data arrangement with rows and column ...
.
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
In the relational model of databases, a primary key is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table. The database creator can choose an existing unique attribute or combinati ...
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
In the relational model of databases, a primary key is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table. The database creator can choose an existing unique attribute or combinati ...
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
In the relational model of databases, a primary key is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table. The database creator can choose an existing unique attribute or combinati ...
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
Immutability of
primary key
In the relational model of databases, a primary key is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table. The database creator can choose an existing unique attribute or combinati ...
s
Since the trigger requires that
primary key
In the relational model of databases, a primary key is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table. The database creator can choose an existing unique attribute or combinati ...
being the same throughout time, it is desirable to either ensure or maximize its immutability, if a
primary key
In the relational model of databases, a primary key is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table. The database creator can choose an existing unique attribute or combinati ...
changed its value, the entity it represents would break its own history.
There are several options to achieve or maximize the
primary key
In the relational model of databases, a primary key is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table. The database creator can choose an existing unique attribute or combinati ...
immutability:
* Use of a
surrogate key
A surrogate key (or synthetic key, pseudokey, entity identifier, factless key, or technical 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'' deri ...
as a
primary key
In the relational model of databases, a primary key is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table. The database creator can choose an existing unique attribute or combinati ...
. Since there is no reason to change a value with no meaning other than identity and uniqueness, it would never change.
* Use of an immutable
natural key
A natural key (also known as business key or domain key) is a type of unique key in a database formed of attributes that exist and are used in the external world outside the database (i.e. in the business domain or domain of discourse). In the re ...
as a
primary key
In the relational model of databases, a primary key is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table. The database creator can choose an existing unique attribute or combinati ...
. In a good database design, a
natural key
A natural key (also known as business key or domain key) is a type of unique key in a database formed of attributes that exist and are used in the external world outside the database (i.e. in the business domain or domain of discourse). In the re ...
which can change should not be considered as a "real"
primary key
In the relational model of databases, a primary key is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table. The database creator can choose an existing unique attribute or combinati ...
.
* Use of a mutable
natural key
A natural key (also known as business key or domain key) is a type of unique key in a database formed of attributes that exist and are used in the external world outside the database (i.e. in the business domain or domain of discourse). In the re ...
as a
primary key
In the relational model of databases, a primary key is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table. The database creator can choose an existing unique attribute or combinati ...
(it is widely discouraged) where changes are propagated in every place where it is a
foreign key
A foreign key is a set of attributes in a table that refers to the primary key of another table, linking these two tables. In the context of relational databases, a foreign key is subject to an inclusion dependency constraint that the tuples ...
. In such a case, the history table should be also affected.
Alternatives
Sometimes the
Slowly changing dimension
In data management and data warehousing, a slowly changing dimension (SCD) is a dimension that stores data which, while generally stable, may change over time, often in an unpredictable manner. This contrasts with a rapidly changing dimension, su ...
is used as a method, this diagram is an example:
See also
*
Relational database
A relational database (RDB) is a database based on the relational model of data, as proposed by E. F. Codd in 1970.
A Relational Database Management System (RDBMS) is a type of database management system that stores data in a structured for ...
*
Primary key
In the relational model of databases, a primary key is a designated attribute (column) that can reliably identify and distinguish between each individual record in a table. The database creator can choose an existing unique attribute or combinati ...
*
Natural key
A natural key (also known as business key or domain key) is a type of unique key in a database formed of attributes that exist and are used in the external world outside the database (i.e. in the business domain or domain of discourse). In the re ...
*
Surrogate key
A surrogate key (or synthetic key, pseudokey, entity identifier, factless key, or technical 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'' deri ...
*
Change data capture
In databases, change data capture (CDC) is a set of software design patterns used to determine and track the data that has changed (the "deltas") so that action can be taken using the changed data. The result is a delta-driven dataset.
CDC is an ...
*
Slowly changing dimension
In data management and data warehousing, a slowly changing dimension (SCD) is a dimension that stores data which, while generally stable, may change over time, often in an unpredictable manner. This contrasts with a rapidly changing dimension, su ...
*
Tuple versioning
Notes
The Log trigger was written by
Laurence R. Ugalde to automatically generate history of transactional databases.
External links
References
{{DEFAULTSORT:Log Trigger
Computer data
Data management
Data modeling
Data warehousing