AuditLog Trigger

For creating audit log this trigger can be used to fire trigger

USE [PCCAS]
GO
/****** Object: Trigger [dbo].[Crime_AuditLog] Script Date: 04/29/2014 18:36:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[Crime_AuditLog] ON [dbo].[crime] FOR INSERT, UPDATE, DELETE
AS

BEGIN TRY
BEGIN
DECLARE @bit int,
@FIELD int,
@maxfield int,
@CHAR int,
@fieldname varchar(128),
@TableName varchar(128),
@PKCols varchar(1000),
@SQL varchar(2000),
@UpdateDate varchar(21),
@UserName varchar(128),
@TYPE char(1),
@PKSelect varchar(1000),
@FKColName varchar(1000),
@PKSelectWhere varchar(1000),
@CaseId int,
@CaseNum varchar(1000),
@MySql nvarchar(2000),
@MySql1 nvarchar(2000);
SELECT @TableName = ‘crime’;
DECLARE @ModifiedByColumn varchar(200);
DECLARE @SQLString nvarchar(200);
DECLARE @ParmDefinition nvarchar(200);
DECLARE @USERID int;

SELECT @ModifiedByColumn = @TableName + ‘_ModifiedBy’;
SELECT @UpdateDate = CONVERT(varchar(8),GETDATE(),112) + ‘ ‘ + CONVERT(varchar(12),GETDATE(),114);

— Action
IF EXISTS(SELECT *
FROM inserted)
BEGIN
IF EXISTS(SELECT *
FROM deleted)
BEGIN
SELECT @TYPE = ‘U’;
END
ELSE
BEGIN
SELECT @TYPE = ‘I’;
END;
END
ELSE
BEGIN
SELECT @TYPE = ‘D’;
END;

— get list of columns
SELECT * INTO #ins
FROM inserted;
SELECT * INTO #del
FROM deleted;

—-changed for Inserting User From Application
SET @SQLString = N’SELECT @UserIDOUT = ‘ + @ModifiedByColumn + ‘
FROM #ins’;
SET @ParmDefinition = N’@UserIDOUT NVARCHAR(100) OUTPUT’;
EXECUTE sp_executesql @SQLString,@ParmDefinition,@UserIDOUT = @USERID OUTPUT;

SELECT @UserName = ISNULL(appUsers_UserName,”)
FROM appusers
WHERE appUsers_ID=@USERID;

— date and user
IF @UserName=”
BEGIN
SELECT @UserName = system_user;
END;
— changed for Inserting User From Application

— Get primary key columns for full outer join
SELECT @PKCols = COALESCE(@PKCols + ‘ and’,’ on’) + ‘ i.’ + c.COLUMN_NAME + ‘ = d.’ + c.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME=@TableName
AND CONSTRAINT_TYPE=’PRIMARY KEY’
AND c.TABLE_NAME=pk.TABLE_NAME
AND c.CONSTRAINT_NAME=pk.CONSTRAINT_NAME;

— Get primary key select for insert
SELECT @PKSelect = COALESCE(@PKSelect + ‘+’,”) + ”'<‘ + COLUMN_NAME + ‘=”+convert(varchar(100),coalesce(i.’ + COLUMN_NAME + ‘,d.’ + COLUMN_NAME + ‘))+”>”’
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME=@TableName
AND CONSTRAINT_TYPE=’PRIMARY KEY’
AND c.TABLE_NAME=pk.TABLE_NAME
AND c.CONSTRAINT_NAME=pk.CONSTRAINT_NAME;

SELECT @PKSelectWhere = COLUMN_NAME + ‘=convert(varchar(100),coalesce(‘ + COLUMN_NAME + ‘, ‘ + COLUMN_NAME + ‘))’
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS pk,INFORMATION_SCHEMA.KEY_COLUMN_USAGE c
WHERE pk.TABLE_NAME=@TableName
AND CONSTRAINT_TYPE=’PRIMARY KEY’
AND c.TABLE_NAME=pk.TABLE_NAME
AND c.CONSTRAINT_NAME=pk.CONSTRAINT_NAME;

–Get Foreign key Field Name
SELECT @FKColName = (
SELECT COL_NAME(fc.parent_object_id,fc.parent_column_id)ColNameCaseID
FROM
sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.OBJECT_ID=fc.constraint_object_id
INNER JOIN sys.tables t
ON t.OBJECT_ID=fc.referenced_object_id
WHERE OBJECT_NAME(f.referenced_object_id)=’invCase’
AND OBJECT_NAME(f.parent_object_id)=@TableName);
IF @PKCols IS NULL
BEGIN
RAISERROR(‘no PK on table %s’,16,-1,@TableName);
RETURN;
END;

SELECT @FIELD = 0,
@maxfield = MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@TableName;
WHILE @FIELD<@maxfield
BEGIN
SELECT @FIELD = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@TableName
AND ORDINAL_POSITION>@FIELD;
SELECT @bit = (@FIELD – 1) % 8 + 1;
SELECT @bit = POWER(2,@bit – 1);
SELECT @CHAR = (@FIELD – 1) / 8 + 1;
IF SUBSTRING(COLUMNS_UPDATED(),@CHAR,1)&@bit>0
OR @TYPE IN(‘I’,’D’)
BEGIN

SELECT @MySql = ‘SELECT @CaseId =’ + @FKColName + ‘ FROM ‘ + @TableName + ‘ WHERE ‘ + @PKSelectWhere;
EXECUTE sp_executesql @MySql,N’@CaseId int OUTPUT’,@CaseId = @CaseId OUTPUT;

SELECT @fieldname = COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME=@TableName
AND ORDINAL_POSITION=@FIELD;
SELECT @SQL = ‘insert Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName, CaseId)’;
SELECT @SQL = @SQL + ‘ select ”’ + @TYPE + ””;
SELECT @SQL = @SQL + ‘,”’ + @TableName + ””;
SELECT @SQL = @SQL + ‘,’ + @PKSelect;
SELECT @SQL = @SQL + ‘,”’ + @fieldname + ””;
SELECT @SQL = @SQL + ‘,convert(varchar(1000),d.’ + @fieldname + ‘)’;
SELECT @SQL = @SQL + ‘,convert(varchar(1000),i.’ + @fieldname + ‘)’;
SELECT @SQL = @SQL + ‘,”’ + @UpdateDate + ””;
SELECT @SQL = @SQL + ‘,”’ + @UserName + ””;
SELECT @SQL = @SQL + ‘,’ + LTRIM(RTRIM(STR(@CaseId))) + ”;
SELECT @SQL = @SQL + ‘ from #ins i full outer join #del d’;
SELECT @SQL = @SQL + @PKCols;
SELECT @SQL = @SQL + ‘ where i.’ + @fieldname + ‘ <> d.’ + @fieldname;
SELECT @SQL = @SQL + ‘ or (i.’ + @fieldname + ‘ is null and d.’ + @fieldname + ‘ is not null)’;
SELECT @SQL = @SQL + ‘ or (i.’ + @fieldname + ‘ is not null and d.’ + @fieldname + ‘ is null)’;
EXEC (@SQL);
END;
END

END
END TRY
BEGIN CATCH
BEGIN
RAISERROR(‘Error Occurred’,16,1);
PRINT ‘Record Deleted — Instead Of Delete Trigger.’
END
END CATCH