TSQL - DML After Trigger for Insert,Update and Delete Operation

Scenario: 

Sometime we have to write a trigger on table to capture changes for different operation such as Insert, Update and Delete. Here is sample Code that can be modified according to the Source Table.

Solution:

I have created dbo.Customer Table as Source Table on which I want to create DML After trigger. After that I have created an Audit Table for dbo.Customer with name dbo.Customer_Audit which is going to save all changes.

USE TestDB
GOCREATE TABLE dbo.Customer
  (
     [CustomerID] INT IDENTITY(1, 1),
     [Name]       VARCHAR(50),
     [ADDRESS]    VARCHAR(100)
  )
GO
CREATE TABLE dbo.Customer_Audit
  (
     [CustomerID]    INT,
     [Name]          VARCHAR(50),
     [ADDRESS]       VARCHAR(100),
     [OperationDate] [DATETIME] NOT NULL,
     [Operation]     [VARCHAR](50) NOT NULL,
     [OperationBy]   [VARCHAR](100) NOT NULL
  )

--Create DML After Trigger

CREATE TRIGGER [dbo].[Tr_Customer_Audit]ON [dbo].[Customer]FOR INSERT, UPDATE, DELETE
AS
    SET NOCOUNT ON;
 --Capture the Operation (Inserted, Deleted Or Updated)

    DECLARE @operation AS VARCHAR(10)
    DECLARE @Count AS INT

    SET @operation = 'Inserted'

    SELECT @Count = COUNT(*)
    FROM   DELETED

    IF @Count > 0
      BEGIN
          SET @operation = 'Deleted'

          SELECT @Count = COUNT(*)
          FROM   INSERTED

          IF @Count > 0
            SET @operation = 'Updated'
      END
--Capturing Delete Operation

    IF @operation = 'Deleted'
      BEGIN
          INSERT INTO dbo.Customer_Audit
                      ([CustomerID],
                       [Name],
                       [ADDRESS],
                       [OperationDate],
                       [Operation],
                       [OperationBy])
          SELECT [CustomerID],
                 [Name],
                 [ADDRESS],
                 GETDATE()    AS [OperationDate],
                 'Deleted'    AS [Operation],
                 Suser_name() AS [OperationBy]
          FROM   deleted
      END
    ELSE
      BEGIN

--Capturing Insert Operation

          IF @operation = 'Inserted'
            BEGIN
                INSERT INTO dbo.Customer_Audit
                            ([CustomerID],
                             [Name],
                             [ADDRESS],
                             [OperationDate],
                             [Operation],
                             [OperationBy])
                SELECT [CustomerID],
                       [Name],
                       [ADDRESS],
                       GETDATE()    AS [OperationDate],
                       'Inserted'   AS [Operation],
                       Suser_name() AS [OperationBy]
                FROM   inserted
            END
          

--Capture Update Operation

          ELSE
            BEGIN
                INSERT INTO dbo.Customer_Audit
                            ([CustomerID],
                             [Name],
                             [ADDRESS],
                             [OperationDate],
                             [Operation],
                             [OperationBy])
                SELECT [CustomerID],
                       [Name],
                       [ADDRESS],
                       GETDATE()    AS [OperationDate],
                       'Updated'    AS [Operation],
                       Suser_name() AS [OperationBy]
                FROM   inserted
            END
      END