question

Hi SSC I have created a dynamic SQL trigger to audit any changes made on a tables values. The trigger works like a charm except for the one table which is accessed repeatedly throughout the day (+/- 3000 hits per min). We need to monitor this table but the trigger is causing a lock which in turn causes our VOIP application doing all the writes to fall over. I have tried to add SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; but with no success. Any idea on how I can get around this issue? USE [ProcessDB] GO /****** Object: StoredProcedure [dbo].[CRT_TableAuditLogging] Script Date: 2014-02-12 02:35:43 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Me -- Create date: 2014/01/23 -- Description: Enable Audit logging on a spesific table -- ============================================= ALTER PROCEDURE [dbo].[CRT_TableAuditLogging] -- Add the parameters for the stored procedure here @DBName VARCHAR(200), @TableName VARCHAR(200), @TriggerName VARCHAR(200) AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here DECLARE @CreateTrigger NVARCHAR(MAX) DECLARE @BigSQL VARCHAR(MAX) SET @CreateTrigger = ' IF OBJECT_ID(''''AuditTrail'''') IS NULL BEGIN CREATE TABLE [dbo].[AuditTrail]( [ID] [int] IDENTITY(1,1) NOT NULL, [TableID] [int] NOT NULL, [UserID] [int] NOT NULL, [FK_Action] [int] NOT NULL, [DateTimeStamp] [datetime] NOT NULL, [OldValue] [xml] NULL, [NewValue] [xml] NULL, CONSTRAINT [PK_AuditTrail] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END ELSE BEGIN PRINT ''''AUDIT TRACKING TABLE ALREADY EXSISTS'''' END ' SET @BigSQL = 'USE ' + @DBName + '; EXEC sp_executesql N''' + @CreateTrigger + ''''; EXEC (@BigSQL) SET @CreateTrigger = ' IF OBJECT_ID(''''' + @TriggerName + ''''', ''''TR'''') IS NOT NULL BEGIN DROP TRIGGER ' + @TriggerName + ' END ELSE BEGIN PRINT ''''The Trigger ' + @TriggerName + ' does not exsist'''' END;' SET @BigSQL = 'USE ' + @DBName + '; EXEC sp_executesql N''' + @CreateTrigger + ''''; EXEC (@BigSQL) SET @CreateTrigger = ' CREATE TRIGGER [dbo].[' + @TriggerName + '] ON [dbo].[' + @TableName + '] AFTER INSERT,UPDATE,DELETE AS BEGIN SET NOCOUNT ON; SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; DECLARE @CapturedBy INT DECLARE @DeletedCount INT DECLARE @InsertCount INT DECLARE @Action INT = 0 DECLARE @InsertedXML XML DECLARE @DeletedXML XML DECLARE @GetColumnList VARCHAR(MAX) DECLARE @ColumnCount INT = 0 DECLARE @CurrentPosition INT = 0 DECLARE @ColumnName VARCHAR(200) DECLARE @TableCompare NVARCHAR(MAX) DECLARE @SelectStatement VARCHAR(MAX) DECLARE @BuildInserted VARCHAR(MAX) DECLARE @BuildDeleted NVARCHAR(MAX) DECLARE @ParmList NVARCHAR(4000) DECLARE @IncludeColumn BIT DECLARE @AffectedTable INT DECLARE @PK_FieldList VARCHAR(MAX) GetUserID: SET @CapturedBy = CAST(REPLACE(CAST(CONTEXT_INFO() AS VARCHAR(10)),CHAR(0),'''''''') AS INT) IF(@CapturedBy) IS NULL BEGIN SELECT @CapturedBy = P.[Code] FROM sys.dm_exec_sessions AS S INNER JOIN [dbo].[Person] AS P ON S.[login_name] = P.[User_Name] WHERE S.[session_id] = @@spid END IF(@CapturedBy) IS NULL BEGIN INSERT INTO [dbo].[Person] ([Name] ,[Active] ,[User_Name] ,[Lowered_User_Name] ,[Creation_Date]) SELECT [login_name] ,''''Y'''' ,[login_name] ,LOWER([login_name]) ,GETDATE() FROM sys.dm_exec_sessions WHERE session_id = @@spid GOTO GetUserID END DECLARE @PK_Fields TABLE ([ColumnName] VARCHAR(200)) SELECT @AffectedTable = parent_obj FROM sysobjects WHERE xtype = ''''tr'''' AND name = OBJECT_NAME(@@PROCID); INSERT @PK_Fields SELECT DISTINCT C.[NAME] FROM sys.key_constraints KC INNER JOIN sys.index_columns IC ON KC.[parent_object_id] = IC.[object_id] INNER JOIN sys.columns C ON IC.[object_id] = C.[object_id] AND IC.[column_id] = C.[column_id] INNER JOIN [INFORMATION_SCHEMA].[KEY_COLUMN_USAGE] AS ISK ON KC.[Name] = ISK.[Constraint_NAME] WHERE KC.[type] = ''''PK'''' AND ISK.[TABLE_NAME] = OBJECT_NAME(@AffectedTable) SELECT @ColumnCount = COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE [TABLE_NAME] = OBJECT_NAME(@AffectedTable) SELECT @DeletedCount = COUNT(*) FROM DELETED SELECT @InsertCount = COUNT(*) FROM INSERTED EXEC [ProcessDB].[dbo].[DROPEXSISTING] ''''##InsertedStorage'''' EXEC [ProcessDB].[dbo].[DROPEXSISTING] ''''##DeletedStorage'''' SELECT * INTO ##InsertedStorage FROM INSERTED SELECT * INTO ##DeletedStorage FROM DELETED IF(@DeletedCount = 0 AND @InsertCount > 0) BEGIN SET @Action = 1; WHILE(@ColumnCount) > @CurrentPosition BEGIN SET @CurrentPosition += 1 SELECT @ColumnName = [COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS WHERE [TABLE_NAME] = OBJECT_NAME(@AffectedTable) AND [ORDINAL_POSITION] = @CurrentPosition SET @TableCompare = '''' SELECT @IncludeColumnOUT = CASE WHEN ['''' + @ColumnName + ''''] IS NULL THEN 0 ELSE 1 END FROM ##InsertedStorage'''' SET @ParmList = N''''@IncludeColumnOUT BIT OUTPUT'''' EXEC sys.sp_executesql @TableCompare ,@ParmList ,@IncludeColumnOUT = @IncludeColumn OUTPUT IF(@IncludeColumn = 1) BEGIN SET @SelectStatement = ISNULL(@SelectStatement,'''''''') + ''''['''' + @ColumnName + ''''], ''''; DELETE FROM @PK_Fields WHERE [ColumnName] = @ColumnName END END END IF(@DeletedCount > 0 AND @InsertCount > 0) BEGIN SET @Action = 2; WHILE(@ColumnCount) > @CurrentPosition BEGIN SET @CurrentPosition += 1 SELECT @ColumnName = [COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS WHERE [TABLE_NAME] = OBJECT_NAME(@AffectedTable) AND [ORDINAL_POSITION] = @CurrentPosition SET @TableCompare = '''' SELECT ['''' + @ColumnName + ''''] INTO #'''' + @ColumnName + '''' FROM ##InsertedStorage EXCEPT SELECT ['''' + @ColumnName + ''''] FROM ##DeletedStorage'''' EXEC(@TableCompare) IF(@@ROWCOUNT > 0) BEGIN SET @SelectStatement = ISNULL(@SelectStatement,'''''''') + ''''['''' + @ColumnName + ''''], ''''; DELETE FROM @PK_Fields WHERE [ColumnName] = @ColumnName END END END IF(@DeletedCount > 0 AND @InsertCount = 0) BEGIN SET @Action = 3 WHILE(@ColumnCount) > @CurrentPosition BEGIN SET @CurrentPosition += 1 SELECT @ColumnName = [COLUMN_NAME] FROM INFORMATION_SCHEMA.COLUMNS WHERE [TABLE_NAME] = OBJECT_NAME(@AffectedTable) AND [ORDINAL_POSITION] = @CurrentPosition SET @TableCompare = '''' SELECT @IncludeColumnOUT = CASE WHEN ['''' + @ColumnName + ''''] IS NULL THEN 0 ELSE 1 END FROM ##DeletedStorage'''' SET @ParmList = N''''@IncludeColumnOUT BIT OUTPUT'''' EXEC sys.sp_executesql @TableCompare ,@ParmList ,@IncludeColumnOUT = @IncludeColumn OUTPUT IF(@IncludeColumn = 1) BEGIN SET @SelectStatement = ISNULL(@SelectStatement,'''''''') + ''''['''' + @ColumnName + ''''], ''''; DELETE FROM @PK_Fields WHERE [ColumnName] = @ColumnName END END END SELECT @PK_FieldList = REPLACE((SELECT ([ColumnName] + ''''|'''') FROM @PK_Fields FOR XML PATH ('''''''')),''''|'''','''','''') SET @PK_FieldList = LEFT(@PK_FieldList,LEN(@PK_FieldList) - 1) SET @SelectStatement = ''''SELECT '''' + CASE ISNULL(@PK_FieldList,''''1'''') WHEN ''''1'''' THEN '''''''' ELSE @PK_FieldList + '''','''' END + LEFT(@SelectStatement,LEN(@SelectStatement) - 1); IF(@Action < 3) BEGIN SET @BuildInserted = @SelectStatement + '''' FROM ##InsertedStorage FOR XML RAW,ELEMENTS,ROOT(''''''''Audit''''''''), TYPE''''; DECLARE @TempInsertedStorage TABLE ([Data] XML) INSERT @TempInsertedStorage EXEC (@BuildInserted) SELECT @InsertedXML = [Data] FROM @TempInsertedStorage END IF(@Action > 1) BEGIN SET @BuildDeleted = @SelectStatement + '''' FROM ##DeletedStorage FOR XML RAW,ELEMENTS,ROOT(''''''''Audit''''''''), TYPE''''; DECLARE @TempDeletedStorage TABLE ([Data] XML) INSERT @TempDeletedStorage EXEC (@BuildDeleted) SELECT @DeletedXML = [Data] FROM @TempDeletedStorage END EXEC [ProcessDB].[dbo].[DROPEXSISTING] ''''##InsertedStorage'''' EXEC [ProcessDB].[dbo].[DROPEXSISTING] ''''##DeletedStorage'''' IF(@DeletedXML IS NULL AND @InsertedXML IS NULL) GOTO EndTrigger INSERT INTO [dbo].[AuditTrail] ([TableID] ,[UserID] ,[FK_Action] ,[DateTimeStamp] ,[OldValue] ,[NewValue]) SELECT @AffectedTable ,@CapturedBy ,@Action ,GETDATE() ,@DeletedXML ,@InsertedXML EndTrigger: END' SET @BigSQL = 'USE ' + @DBName + '; EXEC sp_executesql N''' + @CreateTrigger + ''''; EXEC (@BigSQL) END