SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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 [ ; ] >}
Code

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
)
Code

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
Code

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
)
Code

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
Code

sql-trigger-example

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
Code

SQL Server trigger sample

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.