audit.sql

USE [DatabaseName]

create table #AuditRequired([TableName] nvarchar(50), [PKName] nvarchar(50))
insert into #AuditRequired values ('User', 'UserId')
insert into #AuditRequired values ('Role', 'RoleId')
insert into #AuditRequired values ('Promotion', 'PromotionId')

Declare @tableName nvarchar(50) = ''
Declare @primaryKeyName nvarchar(50) = ''

While (Select Count(*) From #AuditRequired) > 0
Begin

    Select Top 1 @tableName = TableName,  @primaryKeyName = PKName From #AuditRequired
-----------------------------------------------------
-- adding new audit columns to the table and creating trigger to update them
exec ('ALTER TABLE [dbo].[' + @tableName + '] ADD 	ModifyDt datetime NULL, 	ModifyBy nvarchar(50) NULL')
exec ('ALTER TABLE [dbo].[' + @tableName + '] ADD CONSTRAINT 	DF_' + @tableName + '_ModifyDt DEFAULT GETUTCDATE() FOR ModifyDt')
exec ('ALTER TABLE [dbo].[' + @tableName + '] ADD CONSTRAINT 	DF_' + @tableName + '_ModifyBy DEFAULT CURRENT_USER FOR ModifyBy')
exec ('ALTER TABLE [dbo].[' + @tableName + '] SET (LOCK_ESCALATION = TABLE)')

exec('
CREATE TRIGGER [dbo].onRowUpdate_' + @tableName + '    ON  [dbo].[' + @tableName +']    AFTER UPDATE
AS 
BEGIN
   SET NOCOUNT ON;
   UPDATE t SET ModifyDt = getutcdate(), ModifyBy = CURRENT_USER
   FROM [dbo].[' + @tableName + '] AS t
   WHERE EXISTS (SELECT 1 FROM inserted WHERE ' + @primaryKeyName + ' = t.'+ @primaryKeyName + ' );
END
')
-----------------------------------------------------
    Delete #AuditRequired Where TableName = @tableName and PKName = @primaryKeyName
End

drop table #AuditRequired