Create Audit Table and Insert\Update\Delete Triggers for a given table

 

Audit is always a kind of standard requirement in all the trasactional system. For auditing a particular table ,we need to create a similar structure audit table and create Insert , Update and Delete triggers to keep track of the changes in the table. This is utility procedure will create below objects in the database of the passed table.

1. Creates an audit table with name <Tablename>_audit. It has some additional fields to capture the changes on the table

                  AuditDataState   -- Stores the data state whether "New" or "Old"

                  AuditDMLAction   -- Stores the DML Action like "Insert","Update","Delete"

                  AuditUser           -- Stores the User performed the action on the table

                  AuditDateTime    -- Stores the date time on which the action happened.

2. Creates an insert trigger with the name <TableName>_Insert

3. Creates a delete trigger with the name <TableName>_Delete

4. Creates an update trigger with the name <TableName>_Update

This procedure has an option to generate only the scripts and not to generate the objects physically into the database.


Parameters

@Schemaname - SchemaName to which the table belongs to. Default value 'dbo'.

@Tablename - TableName for which the procs needs to be generated.

@GenerateScriptOnly - When passed 1 , this will generate the scripts alone.

When passed 0 , this will create the audit tables and triggers in the current database.

Default value is 1

@ForceDropAuditTable - When passed 1 , will drop the audit table and recreate.When passed 0 , will generate the alter scripts.Default value is 0

@IgnoreExistingColumnMismatch - When passed 1 , will not stop with the error on the mismatch of existing column and will create the trigger.                                        When passed 0 , will stop with the error on the mismatch of existing column.                                                                                                                      Default value is 0

@DontAuditforUsers - Pass the UserName as comma seperated for whom the audit is not required.
      Default value is '' which will do audit for all the users.

@DontAuditforColumns - Pass the ColumnNames as comma seperated for which the audit is not required.
      Default value is '' which will do audit for all the users.

 Usage

To Generate the scripts alone

EXEC GenerateTriggers @Schemaname = 'dbo',@Tablename  = 'TableName', @GenerateScriptOnly = 1

To create the objects in the database

EXEC GenerateTriggers @Schemaname = 'dbo',@Tablename  = 'TableName', @GenerateScriptOnly = 0

To force drop and recreate the audit table

EXEC GenerateTriggers @Schemaname = 'dbo',@Tablename  = 'TableName', @GenerateScriptOnly = 0,@ForceDropAuditTable =1

To ignore and create triggers or generate script 

EXEC GenerateTriggers @Schemaname = 'dbo',@Tablename  = 'TableName', @GenerateScriptOnly = 0,@IgnoreExistingColumnMismatch =1

To avoid some users not to be audited

EXEC GenerateTriggers @Schemaname = 'dbo',@Tablename  = 'TableName', @GenerateScriptOnly = 0,@DontAuditforUsers ='NT\UserName1,NT\UserName2'

To avoid some columns not to be audited in update

EXEC GenerateTriggers @Schemaname = 'dbo',@Tablename  = 'TableName', @GenerateScriptOnly = 0,@DontAuditforUsers ='NT\UserName1,NT\UserName2',@DontAuditforColumns='Column1,Column2'

Revision History

22-Feb-2016 -- > 1) Added a new column in audit table for getting the updated columns.

                        2) Fix for timestamp column issue.

16-Aug-2016 -- > Modified to generate alter table scripts instead of droping and creating the audit table

26-Sep-2016 -- > Fixed issues with script generation with other schema than dbo. For force drop audit table added drop table.

27-Sep-2016 -- > Fixed issues with script generation if it has an identity column.

22-Dec-2016 -- > Added a new parameter to exclude users to be audited. i.e, a row will not be inserted for those users passed in @DontAuditforUsers

21-Apr-2016 --> Added a new parameter to exclude columns to be audited in Update trigger. i.e, a row will not be inserted when those columns passed in @DontAuditforColumns are updated

SQL
IF OBJECT_ID('GenerateTriggers','P') IS NOT NULL   
    DROP PROC GenerateTriggers   
GO   
   
CREATE PROC GenerateTriggers   
 @Schemaname Sysname = 'dbo'   
,@Tablename  Sysname   
,@GenerateScriptOnly    bit = 1  
,@ForceDropAuditTable   bit = 0  
,@IgnoreExistingColumnMismatch   bit = 0  
,@DontAuditforUsers NVARCHAR(4000) =  ''
,@DontAuditforColumns NVARCHAR(4000) =  ''
AS   
   
SET NOCOUNT ON   
   
/*   
Parameters   
@Schemaname            - SchemaName to which the table belongs to. Default value 'dbo'.   
@Tablename            - TableName for which the procs needs to be generated.   
@GenerateScriptOnly - When passed 1 , this will generate the scripts alone..   
                      When passed 0 , this will create the audit tables and triggers in the current database.   
                      Default value is 1   
@ForceDropAuditTable - When passed 1 , will drop the audit table and recreate 
                       When passed 0 , will generate the alter scripts 
                       Default value is 0 
@IgnoreExistingColumnMismatch - When passed 1 , will not stop with the error on the mismatch of existing column and will create the trigger. 
                                When passed 0 , will stop with the error on the mismatch of existing column. 
                                Default value is 0 
@DontAuditforUsers - Pass the UserName as comma seperated for whom the audit is not required.
					 Default value is '' which will do audit for all the users.

@DontAuditforColumns - Pass the ColumnNames as comma seperated for which the audit is not required.
      Default value is '' which will do audit for all the users.
*/   
   
DECLARE @SQL VARCHAR(MAX)   
DECLARE @SQLTrigger VARCHAR(MAX)   
DECLARE @ErrMsg VARCHAR(MAX)
   

DECLARE @AuditTableName SYSNAME   
DECLARE @QuotedSchemaName SYSNAME
DECLARE @QuotedTableName SYSNAME
DECLARE @QuotedAuditTableName SYSNAME
DECLARE @InsertTriggerName SYSNAME
DECLARE @UpdateTriggerName SYSNAME
DECLARE @DeleteTriggerName SYSNAME
DECLARE @QuotedInsertTriggerName SYSNAME
DECLARE @QuotedUpdateTriggerName SYSNAME
DECLARE @QuotedDeleteTriggerName SYSNAME
DECLARE @DontAuditforUsersTmp NVARCHAR(4000)

SELECT @AuditTableName =  @Tablename + '_Audit'   
SELECT @QuotedSchemaName = QUOTENAME(@Schemaname)
SELECT @QuotedTableName = QUOTENAME(@Tablename)
SELECT @QuotedAuditTableName = QUOTENAME(@AuditTableName)
SELECT @InsertTriggerName = @Tablename + '_Insert'  
SELECT @UpdateTriggerName = @Tablename + '_Update'  
SELECT @DeleteTriggerName = @Tablename + '_Delete'  
SELECT @QuotedInsertTriggerName = QUOTENAME(@InsertTriggerName)
SELECT @QuotedUpdateTriggerName = QUOTENAME(@UpdateTriggerName)
SELECT @QuotedDeleteTriggerName = QUOTENAME(@DeleteTriggerName)

IF LTRIM(RTRIM(@DontAuditforUsers)) <> ''
BEGIN

	IF RIGHT(@DontAuditforUsers,1) = ','
	BEGIN
		SELECT @DontAuditforUsersTmp = LEFT(@DontAuditforUsers,LEN(@DontAuditforUsers) -1)
	END
	ELSE
	BEGIN
		SELECT @DontAuditforUsersTmp = @DontAuditforUsers
	END
	
	SELECT @DontAuditforUsersTmp = REPLACE(@DontAuditforUsersTmp,',',''',''')

END


SELECT @DontAuditforColumns =',' + UPPER(@DontAuditforColumns) + ','

IF NOT EXISTS (SELECT 1    
             FROM sys.objects    
            WHERE Name= @TableName   
              AND Schema_id=Schema_id(@Schemaname)   
              AND Type = 'U') 
BEGIN
	SELECT @ErrMsg = @QuotedSchemaName + '.' + @QuotedTableName + ' Table Not Found ' 
	RAISERROR(@ErrMsg ,16,1)
	RETURN
END

   
----------------------------------------------------------------------------------------------------------------------   
-- Audit Create OR Alter table    
----------------------------------------------------------------------------------------------------------------------   
  
DECLARE @ColList VARCHAR(MAX)  
DECLARE @InsertColList VARCHAR(MAX)  
DECLARE @UpdateCheck VARCHAR(MAX)  
 
DECLARE @NewAddedCols TABLE 
( 
 ColumnName SYSNAME 
,DataType   SYSNAME 
,CharLength INT 
,Collation  SYSNAME NULL 
,ChangeType VARCHAR(20) NULL 
,MainTableColumnName SYSNAME  NULL 
,MainTableDataType   SYSNAME  NULL 
,MainTableCharLength INT  NULL 
,MainTableCollation SYSNAME  NULL 
,AuditTableColumnName SYSNAME  NULL 
,AuditTableDataType   SYSNAME  NULL 
,AuditTableCharLength INT  NULL 
,AuditTableCollation SYSNAME  NULL 
) 
 
  
SELECT @ColList = ''  
SELECT @UpdateCheck = ' '  
SELECT @SQL = '' 
SELECT @InsertColList = ''

 
SELECT @ColList = @ColList +   CASE SC.is_identity  
                                           WHEN 1 THEN 'CONVERT(' + ST.name + ',' + QUOTENAME(SC.name) + ') as ' + QUOTENAME(SC.name)  
                                           ELSE QUOTENAME(SC.name)  
                                           END + ','  
				, @InsertColList = @InsertColList + QUOTENAME(SC.name) + ','
                ,  @UpdateCheck = @UpdateCheck + 
								  CASE 
								  WHEN CHARINDEX(',' + UPPER(SC.NAME) + ',',@DontAuditforColumns) = 0 THEN 'CASE WHEN UPDATE(' + QUOTENAME(SC.name) + ') THEN ''' + QUOTENAME(SC.name) + '-'' ELSE '''' END + ' +  CHAR(10)  
								  ELSE ''
								  END
              FROM SYS.COLUMNS SC  
              JOIN SYS.OBJECTS SO  
                ON SC.object_id = SO.object_id     
              JOIN SYS.schemas SCH  
                ON SCH.schema_id = SO.schema_id  
              JOIN SYS.types ST  
                ON ST.user_type_id = SC.user_type_id  
               AND ST.system_type_id = SC.system_type_id   
             WHERE SCH.Name = @Schemaname   
               AND SO.name  = @Tablename   
               AND UPPER(ST.name)  <> UPPER('timestamp')  
  
            SELECT @ColList = SUBSTRING(@ColList,1,LEN(@ColList)-1)  
			SELECT @UpdateCheck = SUBSTRING(@UpdateCheck,1,LEN(@UpdateCheck)-3)  
			SELECT @InsertColList = SUBSTRING(@InsertColList,1,LEN(@InsertColList)-1)  
 
            SELECT @InsertColList = @InsertColList + ',AuditDataState,AuditDMLAction,AuditUser,AuditDateTime,UpdateColumns' 
 
IF EXISTS (SELECT 1    
             FROM sys.objects    
            WHERE Name= @AuditTableName   
              AND Schema_id=Schema_id(@Schemaname)   
              AND Type = 'U')  AND @ForceDropAuditTable = 0 
 BEGIN 
 
            ----------------------------------------------------------------------------------------------------------------------   
            -- Get the comparision metadata for Main and Audit Tables 
            ----------------------------------------------------------------------------------------------------------------------   
 
            INSERT INTO @NewAddedCols 
            (ColumnName,DataType,CharLength,Collation,ChangeType,MainTableColumnName 
            ,MainTableDataType,MainTableCharLength,MainTableCollation,AuditTableColumnName,AuditTableDataType,AuditTableCharLength,AuditTableCollation) 
            SELECT ISNULL(MainTable.ColumnName,AuditTable.ColumnName) 
                  ,ISNULL(MainTable.DataType,AuditTable.DataType) 
                  ,ISNULL(MainTable.CharLength,AuditTable.CharLength) 
                  ,ISNULL(MainTable.Collation,AuditTable.Collation) 
                  ,CASE  
                   WHEN MainTable.ColumnName IS NULL THEN 'Deleted' 
                   WHEN AuditTable.ColumnName IS NULL THEN 'Added' 
                   ELSE NULL 
                   END  
                  ,MainTable.ColumnName 
                  ,MainTable.DataType 
                  ,MainTable.CharLength 
                  ,MainTable.Collation 
                  ,AuditTable.ColumnName 
                  ,AuditTable.DataType 
                  ,AuditTable.CharLength 
                  ,AuditTable.Collation 
              FROM 
     
            ( 
            SELECT SC.Name As ColumnName,ST.Name as DataType,SC.is_identity as isIdentity,SC.Max_length as CharLength,SC.Collation_Name as Collation 
              FROM SYS.COLUMNS SC  
              JOIN SYS.OBJECTS SO  
                ON SC.object_id = SO.object_id     
              JOIN SYS.schemas SCH  
                ON SCH.schema_id = SO.schema_id  
              JOIN SYS.types ST  
                ON ST.user_type_id = SC.user_type_id  
               AND ST.system_type_id = SC.system_type_id   
             WHERE SCH.Name = @Schemaname   
               AND SO.name  = @Tablename   
               AND UPPER(ST.name)  <> UPPER('timestamp')  
            ) MainTable 
            FULL OUTER JOIN 
            ( 
            SELECT SC.Name As ColumnName,ST.Name as DataType,SC.is_identity as isIdentity,SC.Max_length as CharLength,SC.Collation_Name as Collation 
              FROM SYS.COLUMNS SC  
              JOIN SYS.OBJECTS SO  
                ON SC.object_id = SO.object_id     
              JOIN SYS.schemas SCH  
                ON SCH.schema_id = SO.schema_id  
              JOIN SYS.types ST  
                ON ST.user_type_id = SC.user_type_id  
               AND ST.system_type_id = SC.system_type_id   
             WHERE SCH.Name = @Schemaname   
               AND SO.name  = @AuditTableName   
               AND UPPER(ST.name)  <> UPPER('timestamp')  
               AND SC.Name NOT IN ('AuditDataState','AuditDMLAction','AuditUser','AuditDateTime','UpdateColumns') 
            ) AuditTable 
            ON MainTable.ColumnName = AuditTable.ColumnName 
          
         ----------------------------------------------------------------------------------------------------------------------   
        -- Find data type changes between table 
        ----------------------------------------------------------------------------------------------------------------------   
 
            IF EXISTS ( SELECT * FROM @NewAddedCols NC 
                         WHERE NC.MainTableColumnName = NC.AuditTableColumnName 
                           AND ( 
                               NC.MainTableDataType   <> NC.AuditTableDataType 
                               OR 
                               NC.MainTableCharLength  > NC.AuditTableCharLength 
                               OR 
                               NC.MainTableCollation  <> NC.AuditTableCollation 
                               ) 
                ) 
            BEGIN 
                SELECT CONVERT(VARCHAR(50), 
                           CASE 
                           WHEN NC.MainTableDataType   <> NC.AuditTableDataType   THEN 'DataType Mismatch' 
                           WHEN NC.MainTableCharLength  > NC.AuditTableCharLength THEN 'Length in maintable is greater than Audit Table' 
                           WHEN NC.MainTableCollation  <> NC.AuditTableCollation  THEN 'Collation Difference' 
                           END) AS Mismatch 
                      ,NC.MainTableColumnName 
                      ,NC.MainTableDataType 
                      ,NC.MainTableCharLength 
                      ,NC.MainTableCollation 
                      ,NC.AuditTableColumnName 
                      ,NC.AuditTableDataType 
                      ,NC.AuditTableCharLength 
                      ,NC.AuditTableCollation 
                  FROM @NewAddedCols NC 
                 WHERE NC.MainTableColumnName = NC.AuditTableColumnName 
                   AND ( 
                       NC.MainTableDataType   <> NC.AuditTableDataType 
                       OR 
                       NC.MainTableCharLength  > NC.AuditTableCharLength 
                       OR 
                       NC.MainTableCollation  <> NC.AuditTableCollation 
                       ) 
 
                RAISERROR('There are differences in Datatype or Lesser Length or Collation difference between the Main table and Audit Table. Please refer the output',16,1) 
                IF @IgnoreExistingColumnMismatch = 0 
                BEGIN 
                    RETURN 
                END 
            END 
 
        ----------------------------------------------------------------------------------------------------------------------   
        -- Find the new and deleted columns  
        ----------------------------------------------------------------------------------------------------------------------   
 
          IF EXISTS(SELECT * FROM @NewAddedCols WHERE ChangeType IS NOT NULL) 
          BEGIN 
 
                SELECT @SQL = @SQL +   'ALTER TABLE ' + @QuotedSchemaName + '.' + @QuotedAuditTableName  
                                   + CASE 
                                     WHEN NC.ChangeType ='Added' THEN ' ADD '  + QUOTENAME(NC.ColumnName) + ' ' + NC.DataType + ' '  
                                     +  CASE  
                                        WHEN NC.DataType IN ('char','varchar','nchar','nvarchar') AND NC.CharLength = -1 THEN '(max) COLLATE ' + NC.Collation + ' NULL ' 
                                        WHEN NC.DataType IN ('char','varchar') THEN '(' + CONVERT(VARCHAR(5),NC.CharLength) + ') COLLATE ' + NC.Collation + ' NULL ' 
                                        WHEN NC.DataType IN ('nchar','nvarchar') THEN '(' + CONVERT(VARCHAR(5),NC.CharLength/2) + ') COLLATE ' + NC.Collation + ' NULL ' 
                                        ELSE '' 
                                        END 
                                     WHEN NC.ChangeType ='Deleted' THEN ' DROP COLUMN '  + QUOTENAME(NC.ColumnName)  
                                     END + CHAR(10) 
                                      
                  FROM @NewAddedCols NC 
                  WHERE NC.ChangeType IS NOT NULL 
          END 
 
 
 END 
 ELSE 
 BEGIN 
  
            SELECT @SQL = '  IF EXISTS (SELECT 1    
										  FROM sys.objects    
									     WHERE Name=''' + @AuditTableName + '''   
										   AND Schema_id=Schema_id(''' + @Schemaname + ''')   
										   AND Type = ''U'')   
							DROP TABLE ' + @QuotedSchemaName + '.' + @QuotedAuditTableName + '
  
                    SELECT ' + @ColList + '   
                        ,AuditDataState=CONVERT(VARCHAR(10),'''')    
                        ,AuditDMLAction=CONVERT(VARCHAR(10),'''')     
                        ,AuditUser =CONVERT(SYSNAME,'''')   
                        ,AuditDateTime=CONVERT(DATETIME,''01-JAN-1900'')   
                        ,UpdateColumns = CONVERT(VARCHAR(MAX),'''')  
                        Into ' + @QuotedSchemaName + '.' + @QuotedAuditTableName + '   
                    FROM ' + @QuotedSchemaName + '.' + @QuotedTableName +'   
                    WHERE 1=2 '   
END 


 
IF @GenerateScriptOnly = 1   
BEGIN   
    PRINT REPLICATE ('-',200)   
    PRINT '--Create \ Alter Script Audit table for ' + @QuotedSchemaName + '.' + @QuotedTableName   
    PRINT REPLICATE ('-',200)   
    PRINT @SQL   
    IF LTRIM(RTRIM(@SQL)) <> '' 
    BEGIN 
        PRINT 'GO'  
    END 
    ELSE 
    BEGIN 
        PRINT '-- No changes in table structure' 
    END  
END   
ELSE   
BEGIN   
    IF RTRIM(LTRIM(@SQL)) = '' 
    BEGIN 
        PRINT 'No Table Changes Found'  
    END 
    ELSE 
    BEGIN 
        PRINT 'Creating \ Altered Audit table for ' + @QuotedSchemaName + '.' + @QuotedTableName   
        EXEC(@SQL)   
        PRINT 'Audit table ' + @QuotedSchemaName + '.' + @QuotedAuditTableName + ' Created \ Altered succesfully'   
    END 
END   
   
   
----------------------------------------------------------------------------------------------------------------------   
-- Create Insert Trigger   
----------------------------------------------------------------------------------------------------------------------   
   
   
SELECT @SQL = '   
IF EXISTS (SELECT 1    
             FROM sys.objects    
            WHERE Name=''' + @Tablename + '_Insert' + '''   
              AND Schema_id=Schema_id(''' + @Schemaname + ''')   
              AND Type = ''TR'')   
DROP TRIGGER ' + @QuotedSchemaName + '.' + @QuotedInsertTriggerName    
SELECT @SQLTrigger = '   
CREATE TRIGGER '  + @QuotedSchemaName + '.' + @QuotedInsertTriggerName + '
ON '  + @QuotedSchemaName + '.' + @QuotedTableName + '   
FOR INSERT   
AS   
' 


IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> '' 
BEGIN

SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' IF SUSER_NAME() NOT IN (''' + @DontAuditforUsersTmp + ''')'
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' BEGIN'

END



 
SELECT @SQLTrigger =  @SQLTrigger  + CHAR(10) + ' INSERT INTO ' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CHAR(10) +  
 '(' + @InsertColList + ')' + CHAR(10) +  
 'SELECT ' + @ColList + ',''New'',''Insert'',SUSER_SNAME(),getdate(),''''  FROM INSERTED '   

 IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> '' 
BEGIN

	SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' END'

END

   
IF @GenerateScriptOnly = 1   
BEGIN   
    PRINT REPLICATE ('-',200)   
    PRINT '--Create Script Insert Trigger for ' +  @QuotedSchemaName + '.' + @QuotedTablename   
    PRINT REPLICATE ('-',200)   
    PRINT @SQL   
    PRINT 'GO'   
    PRINT @SQLTrigger   
    PRINT 'GO'   
END   
ELSE   
BEGIN   
    PRINT 'Creating Insert Trigger ' + @QuotedInsertTriggerName + '  for ' + @QuotedSchemaName + '.' + @QuotedTablename   
    EXEC(@SQL)   
    EXEC(@SQLTrigger)   
    PRINT 'Trigger ' + @QuotedSchemaName + '.' + @QuotedInsertTriggerName  + ' Created succesfully'   
END   
   
   
----------------------------------------------------------------------------------------------------------------------   
-- Create Delete Trigger   
----------------------------------------------------------------------------------------------------------------------   
   
   
SELECT @SQL = '   
   
IF EXISTS (SELECT 1    
             FROM sys.objects    
            WHERE Name=''' + @Tablename + '_Delete' + '''   
              AND Schema_id=Schema_id(''' + @Schemaname + ''')   
              AND Type = ''TR'')   
DROP TRIGGER ' + @QuotedSchemaName + '.' + + @QuotedDeleteTriggerName + '   
'   
   
SELECT @SQLTrigger =    
'   
CREATE TRIGGER '  + @QuotedSchemaName + '.'  + @QuotedDeleteTriggerName + '   
ON '+ @QuotedSchemaName + '.' + @QuotedTableName + '   
FOR DELETE   
AS   '

IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> '' 
BEGIN

SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' IF SUSER_NAME() NOT IN (''' + @DontAuditforUsersTmp + ''')'
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' BEGIN'

END


 
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + '  INSERT INTO ' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CHAR(10) +  
 '(' + @InsertColList + ')' + CHAR(10) +  
 'SELECT ' + @ColList + ',''Old'',''Delete'',SUSER_SNAME(),getdate(),''''  FROM DELETED'   

IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> '' 
BEGIN

	SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' END'

END

   
IF @GenerateScriptOnly = 1   
BEGIN   
    PRINT REPLICATE ('-',200)   
    PRINT '--Create Script Delete Trigger for ' + @QuotedSchemaName + '.' + @QuotedTableName   
    PRINT REPLICATE ('-',200)   
    PRINT @SQL   
    PRINT 'GO'   
    PRINT @SQLTrigger   
    PRINT 'GO'   
END   
ELSE   
BEGIN   
    PRINT 'Creating Delete Trigger ' + @QuotedDeleteTriggerName + '  for ' + @QuotedSchemaName + '.' + @QuotedTableName   
    EXEC(@SQL)   
    EXEC(@SQLTrigger)   
    PRINT 'Trigger ' + @QuotedSchemaName + '.' + @QuotedDeleteTriggerName + ' Created succesfully'   
END   
   
----------------------------------------------------------------------------------------------------------------------   
-- Create Update Trigger   
----------------------------------------------------------------------------------------------------------------------   
   
   
SELECT @SQL = '   
   
IF EXISTS (SELECT 1    
             FROM sys.objects    
            WHERE Name=''' + @Tablename + '_Update' + '''   
              AND Schema_id=Schema_id(''' + @Schemaname + ''')   
              AND Type = ''TR'')   
DROP TRIGGER ' + @QuotedSchemaName + '.' + @QuotedUpdateTriggerName + '   
'   
   
SELECT @SQLTrigger =   
'   
CREATE TRIGGER ' + @QuotedSchemaName + '.'  + @QuotedUpdateTriggerName + '     
ON '+ @QuotedSchemaName + '.' + @QuotedTableName + '   
FOR UPDATE   
AS '

IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> '' 
BEGIN

SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' IF SUSER_NAME() NOT IN (''' + @DontAuditforUsersTmp + ''')'
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' BEGIN'

END


 
SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + '  

	DECLARE @UpdatedCols varchar(max)

   SELECT @UpdatedCols = ' + @UpdateCheck + '
   
   IF LTRIM(RTRIM(@UpdatedCols)) <> ''''
   BEGIN
		  INSERT INTO ' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CHAR(10) +  
		 '(' + @InsertColList + ')' + CHAR(10) +  
		 'SELECT ' + @ColList +',''New'',''Update'',SUSER_SNAME(),getdate(),@UpdatedCols  FROM INSERTED    
   
		  INSERT INTO ' + @QuotedSchemaName + '.' + @QuotedAuditTableName + CHAR(10) +  
		 '(' + @InsertColList + ')' + CHAR(10) +  
		 'SELECT ' + @ColList +',''Old'',''Update'',SUSER_SNAME(),getdate(),@UpdatedCols  FROM DELETED 
   END'   

IF LTRIM(RTRIM(@DontAuditforUsersTmp)) <> '' 
BEGIN

	SELECT @SQLTrigger = @SQLTrigger + CHAR(10) + ' END'

END


   
IF @GenerateScriptOnly = 1   
BEGIN   
    PRINT REPLICATE ('-',200)   
    PRINT '--Create Script Update Trigger for ' + @QuotedSchemaName + '.' + @QuotedTableName   
    PRINT REPLICATE ('-',200)   
    PRINT @SQL   
    PRINT 'GO'   
    PRINT @SQLTrigger   
    PRINT 'GO'   
END   
ELSE   
BEGIN   
    PRINT 'Creating Delete Trigger ' + @QuotedUpdateTriggerName + '  for ' + @QuotedSchemaName + '.' + @QuotedTableName   
    EXEC(@SQL)   
    EXEC(@SQLTrigger)   
    PRINT 'Trigger ' + @QuotedSchemaName + '.' + @QuotedUpdateTriggerName + '  Created succesfully'   
END   
   
SET NOCOUNT OFF   
   
go





 
Src: https://gallery.technet.microsoft.com/scriptcenter/Create-Audit-Table-and-5cd69d5d

Comments

Post a Comment

Popular posts from this blog

Data Import Best Practices in Power BI

Image
  When you create a data model in Power BI, you should consider how to properly use naming convention and what columns to include, in order to improve usability and performance. This article provides a quick list of best practices valid for both Power BI and Power Pivot. APR 5, 2018 UPDATED Marco Russo DAX     POWER BI     POWER PIVOT     TABULAR    Not all of the suggestions described can be applied to all of your data models. You should adapt these best practices to your specific scenario, looking at how to achieve the goals that are the reason for a certain pattern more than barely apply it without considering the pros and cons of each choice. Even if the article mentions Power BI, all the best practices described are valid for Power Pivot and Analysis Services Tabular models, too. The demo file you can download (at the end of the article) contains a sample Power BI file and the corresponding views defined in a SQL file for ...
Read more

ASP.NET MVC + AdminLTE

Image
  ASP.NET MVC + AdminLTE March 31, 2018 00:14 ASP.NET MVC + AdminLTE ( edit ) ASP.NET MVC Application with a Custom Layout ( Login screen ) https://code.msdn.microsoft.com/ASPNET-MVC-Application-b4b0dc3f A NuGet package for integrating Admin LTE 2.0.4 into ASP.Net MVC 5 https://github.com/eralston/AdminLteMvc  (AdminLTE version 2.0.4) ASP.NET  Example Version of Beautiful AdminLTE Template https://github.com/go2ismail/AdminLTE-ASP-NET-MVC  (AdminLTE version 2.4.0) Tutorials & Samples on adding Charts into ASP.NET MVC Applications https://canvasjs.com/docs/charts/integration/asp-net-mvc-charts/  (Chart) CRUD Operations in ASP.NET MVC Using AJAX and Bootstrap https://dzone.com/articles/crud-operation-in-aspnet-mvc-using-ajax-and-bootst AdminLTE +  ASP.NET MVC 5 + Net Framework 4.8 https://github.com/DiomedesDominguez/AdminLTE.NET ASP.NET MVC 5 Scaffolding https://github.com/robinli/MVC5-Scaffolder ASP.NET MVC 5 SmartCode Scaffolding ( HAY HAY HA...
Read more

Build your first Azure Dara Factory Pipeline

Image
Overview We're going to build a pipeline using the Copy Data tool. This tool makes it easier for people starting out with ADF to create their first pipelines. Before we start, we need to make sure some prerequisites are met. Prerequisites If you haven't already, follow the steps of the  previous part of the tutorial  to set up ADF, a storage account with a blob container and an Azure SQL DB. In the Azure Portal, go to your storage account and then to the "data-input" container we created. Click on the  Upload  link. A pane will open where you can select a local file. Upload the  Customers.csv  file, which you can  download here . Click  Upload  to put the file in the blob container. Create the Pipeline Go to ADF Studio and click on the  Ingest  tile. This will open the  Copy Data tool . In the first step, we can choose to simply copy data from one location to another, or to create a more dynamic, metadata-driven task. We'll cho...
Read more