Sometimes, it is not only important to know when and who perform the last change but trace all the changes to the data in certain important tables.

Big applications, require security behaviors and traceability. And better more important, they must be maintained independently of the applications, since more than one application could modify the same database.

And here is where triggers come to rescue.

Of course, I am not saying you must implement business rules in triggers (or even in Stored Procedures).

Business rules are rules, not data manipulation, even when in some SPECIAL cases, you must implement some in database objects, but this is not the generic case. (We will discuss this in several other posts in the future).

How it works

To store any change, we need a COPY of the data each time it is about changed. So, we need another table with the same definition than the original. Moreover, it is good to store who and when the change was performed.

The stored procedure creates a new table, based in the source table definition, adding the following columns.

Column	Data Type	Usage
DateChanged	datetime2(7)	When
UserChanged	nvarchar(150)	Who
Action	nvarchar(15)	How

The stored procedure receives an optional parameter for the destination schema. If it is provided, the table denomination will be the destination schema and the same table name than the original. If not, then the history table will be named like the source one, plus the “_Hist” suffix.

Once the table exists, the procedure must create Triggers for Insert, update and delete changes over the source table.

How the triggers work?

A trigger is a T-SQL code which is executed automatically when something happens with one or more rows in a table.
The code could be linked to one or more of the three major events: Insertion, update or deletion.
Meanwhile the trigger is running, a special table, called inserted, contains the new values in change, and another table, called delete, contains the old values. Notice that this tables could contains more than one row, in case the action that start the process, manipulates one or more rows, like in one massive update.
From the trigger point of view, there is no update, but a delete and insert combined.
So, inside the trigger, depending on the action, you can have one or both “virtual” tables, as follows.

Action	inserted	deleted
Insert	X
Update	X	X
Delete		X

In this example, the trigger will store in the historical table a new entry with the row (or rows) are inserted. And entries with one or more deleted rows in updates or deletes, to persist the older version of the rows.

CREATE TRIGGER [dbo].[EmployeeTerritories_TInsert] 
	ON [dbo].[EmployeeTerritories]
	AFTER INSERT
AS
  BEGIN
    SET NOCOUNT ON;
	--Insert a new ow in historical table
    INSERT INTO [dbo].[EmployeeTerritories_Hist]
      (
       [EmployeeID]
     , [TerritoryID]
     , [DateChanged]
     , [UserChanged]
     , [Action]
      )
           SELECT 
              [O].[EmployeeID]
            , [O].[TerritoryID]
            , SYSUTCDATETIME()-- The exact moment of the insert
            , USER_NAME()-- The user performing the Insert
            , 'Insert'
           FROM 
              [inserted] [O];
  END;

CREATE TRIGGER [dbo].[EmployeeTerritories_TUD] 
	ON [dbo].[EmployeeTerritories]
	AFTER UPDATE, DELETE
AS
  BEGIN
    DECLARE 
       @Action NVARCHAR(15) = 'Update';
	   /*If there is no rows in inserted table,
	   then it is not an update*/
 
    IF NOT EXISTS
     (
       SELECT 
          *
       FROM 
          [inserted]
     )
      BEGIN
        SET @Action = 'Delete';
      END;
    SET NOCOUNT ON;
    INSERT INTO [dbo].[EmployeeTerritories_Hist]
      (
       [EmployeeID]
     , [TerritoryID]
     , [DateChanged]
     , [UserChanged]
     , [Action]
      )
           SELECT 
              [O].[EmployeeID]
            , [O].[TerritoryID]
            , SYSUTCDATETIME()
            , USER_NAME()
            , @Action
           FROM 
              [deleted] [O];
  END;

So, using the same methodology than in the SP Builder post, with a Table Type variable to collect the columns of the original table, the procedure builds the triggers’ scripts to create them with sp_executesql .

You can found the script fot the SP Create Triggers here.

HTH