SQL Trigger : SQL Server Trigger Example to Log Changes History of a SQL Table
In this T-SQL tutorial sql administrators and tsql developers will find SQL Trigger example code created for logging of updated or deleted records into history tables.
The SQL Server trigger will be created as sql update / delete trigger on the target database table.
For example when the web application or users create/insert record into sql table or delete record from sql database table, the sample sql trigger will execute.
The sql trigger will insert the deleted or updated version of the table row into history table.
T-SQL Create Trigger Syntax
Here is the T-SQL CREATE TRIGGER syntax which is from SQL Server 2008 R2 Books Online (BOL).
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
{sql_statement [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] >}
The above Create Trigger syntax is also valid for MS SQL Server 2008, SQL Server 2005 and also valid for SQL Server 2000 database instances.
SQL Trigger Example
Let's create sql tables that sql developers will use in this sql trigger tutorial.
The first database table CustomerCreditLimits will contain sensitive data.
Your business requirements orders to keep the changes in this sensitive information.
So your tsql developers and SQL Server administrators decided to create sql trigger in order to log every change on records in CustomerCreditLimits sql tables.
Below I coped the CREATE TABLE sql scripts to use with tsql trigger example in this tutorial.
The first sql database table CustomerCreditLimits is used to save active sensitive data.
The second sql table CustomerCreditLimitsHistory is used to keep the history of changes and logs of the data kept in CustomerCreditLimits target sql table.
CREATE TABLE CustomerCreditLimits (
Id int Identity(1,1),
CustomerId int,
CreditLimit int,
ValidFrom datetime,
ValidTo datetime,
InsertDate datetime,
InsertedByUserId int
)
CREATE TABLE CustomerCreditLimitsHistory (
HistoryId int Identity(1,1),
ReferenceId int,
CustomerId int,
CreditLimit int,
ValidFrom datetime,
ValidTo datetime,
InsertDate datetime,
InsertedByUserId int,
UpdatedDate datetime,
UpdatedByUserId int,
DeletedDate datetime,
DeletedByUserId int
)
Here is the t-sql code used to create sql trigger which will execute automatically after sql Update and Delete statements on the target database table.
CREATE TRIGGER dbo.LogCustomerCreditLimitChanges
ON dbo.CustomerCreditLimits
AFTER UPDATE, DELETE
AS
IF EXISTS (
SELECT * FROM Inserted
)
-- UPDATE Statement was executed
INSERT INTO CustomerCreditLimitsHistory (
ReferenceId,
CustomerId,
CreditLimit,
ValidFrom,
ValidTo,
InsertDate,
InsertedByUserId,
UpdatedDate,
UpdatedByUserId
)
SELECT
d.Id,
d.CustomerId,
d.CreditLimit,
d.ValidFrom,
d.ValidTo,
d.InsertDate,
d.InsertedByUserId,
i.InsertDate,
i.InsertedByUserId
FROM Deleted d
INNER JOIN Inserted i ON i.Id = d.Id
ELSE
-- DELETE Statement was executed
INSERT INTO CustomerCreditLimitsHistory (
ReferenceId,
CustomerId,
CreditLimit,
ValidFrom,
ValidTo,
InsertDate,
InsertedByUserId,
DeletedDate,
DeletedByUserId
)
SELECT
Id,
CustomerId,
CreditLimit,
ValidFrom,
ValidTo,
InsertDate,
InsertedByUserId,
GETDATE(),
USER_ID()
FROM Deleted
GO
SQL developers can create sql trigger executing the above sample TSQL script.
After sql developers create example SQL Server trigger, we can continue to this sql tutorial with inserting records to sample sql tables.
INSERT INTO CustomerCreditLimits (
CustomerId, CreditLimit, ValidFrom, ValidTo, InsertDate, InsertedByUserId
) VALUES (
100, 20000, '20100101', '20120101', GETDATE(), 3
)
After we insert the first record into sql table using the above INSERT INTO statement, we can execute SELECT statement on both CustomerCreditLimits and CustomerCreditLimitsHistory history tables. Although there is one record in CustomerCreditLimits sql table, in CustomerCreditLimitsHistory database table there is no record yet. Actually we do not expect any record to be inserted into CustomerCreditLimitsHistory sql database table since we defined the sample sql trigger as sql Update and sql Delete trigger. This means that if an sql Update statement or if any sql Delete statement is executed on the target sql table CustomerCreditLimits, then and only then the SQL Server trigger named LogCustomerCreditLimitChanges will be executed automatically. The execution of LogCustomerCreditLimitChanges sql trigger can add log record in the log history table CustomerCreditLimitsHistory.
Let's now make this sql example.
Let's increase the credit limit by updating the customer credit information using the below sql update code.
Then execute SELECT statements in order to see the data in both sql tables.
UPDATE CustomerCreditLimits
SET
CreditLimit = 50000,
InsertDate = GETDATE(),
InsertedByUserId = 7
WHERE CustomerId = 100
SELECT * FROM CustomerCreditLimits
SELECT * FROM CustomerCreditLimitsHistory
As you can see, after the sql update, the old values of the updated row in the sql table CustomerCreditLimits is logged into the history table CustomerCreditLimitsHistory.
Now the values before update are stored at CustomerCreditLimitsHistory log table.
And new values after update are in CustomerCreditLimits sql database table.
Let's now change the sample and delete the credit info database entry for customer number 100.
Remember that after sql DELETE statement, the SQL Server trigger will execute since we defined the sql trigger als as tsql DELETE trigger.
DELETE CustomerCreditLimits WHERE CustomerId = 100
SELECT * FROM CustomerCreditLimits
SELECT * FROM CustomerCreditLimitsHistory
As you can see in the above screenshot, the deleted customer credit record is also logged into the sql history table with the help of SQL Server trigger example given in this sql tutorial.