Trigger Performance

Last Post 23 Jun 2011 08:39 AM by russellb. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages Not Resolved
rerichards
New Member
New Member

--
22 Jun 2011 08:16 PM
We have multiple triggers similar to the following for audting purposes that po***tes a single audit table:

CREATE TRIGGER [dbo].[MyTable_Audit_U] ON [dbo].[MyTable] FOR UPDATE
AS
SET XACT_ABORT ON
SET NOCOUNT ON

DECLARE @intTableID INT
DECLARE @dtmNow DATETIME
SET @intTableID = OBJECT_ID('MyTable')
SET @dtmNow = GETDATE()

IF UPDATE(Col_A) INSERT dbo.MyAuditTable (KeyID, TableID, ItemID, ColumnName, OldValue, NewValue, SystemUsername, ChangedAt, ChangedBy) SELECT d.KeyID, @intTableID, d.MyTable_UID, 'Col_A', RTrim(Cast(d.Col_A AS varchar(255))), RTrim(Cast(i.Col_A AS varchar(255))), SYSTEM_USER, CASE WHEN DateAdd(minute, 10, i.ChangedAt) < @dtmNow OR i.ChangedAt IS NULL THEN @dtmNow ELSE i.ChangedAt END, CASE WHEN DateAdd(minute, 10, i.ChangedAt) < @dtmNow OR i.ChangedBy IS NULL THEN '' ELSE i.ChangedBy END FROM inserted i INNER JOIN deleted d ON d.MyTable_UID = i.MyTable_UID AND d.KeyID = i.KeyID WHERE (d.Col_A IS NULL AND i.Col_A IS NOT NULL) OR (d.Col_A IS NOT NULL AND i.Col_A IS NULL) OR d.Col_A != i.Col_A

IF UPDATE(Col_D) INSERT dbo.dbo.MyAuditTable (KeyID, TableID, ItemID, ColumnName, OldValue, NewValue, SystemUsername, ChangedAt, ChangedBy) SELECT d.KeyID, @intTableID, d.MyTable_UID, 'Col_D', RTrim(Cast(d.Col_D AS varchar(255))), RTrim(Cast(i.Col_D AS varchar(255))), SYSTEM_USER, CASE WHEN DateAdd(minute, 10, i.ChangedAt) < @dtmNow OR i.ChangedAt IS NULL THEN @dtmNow ELSE i.ChangedAt END, CASE WHEN DateAdd(minute, 10, i.ChangedAt) < @dtmNow OR i.ChangedBy IS NULL THEN '' ELSE i.ChangedBy END FROM inserted i INNER JOIN deleted d ON d.MyTable_UID = i.MyTable_UID AND d.KeyID = i.KeyID WHERE (d.Col_D IS NULL AND i.Col_D IS NOT NULL) OR (d.Col_D IS NOT NULL AND i.Col_D IS NULL) OR d.Col_D != i.Col_D

IF UPDATE(Col_F) INSERT dbo.dbo.MyAuditTable (KeyID, TableID, ItemID, ColumnName, OldValue, NewValue, SystemUsername, ChangedAt, ChangedBy) SELECT d.KeyID, @intTableID, d.MyTable_UID, 'Col_F', RTrim(Cast(d.Col_F AS varchar(255))), RTrim(Cast(i.Col_F AS varchar(255))), SYSTEM_USER, CASE WHEN DateAdd(minute, 10, i.ChangedAt) < @dtmNow OR i.ChangedAt IS NULL THEN @dtmNow ELSE i.ChangedAt END, CASE WHEN DateAdd(minute, 10, i.ChangedAt) < @dtmNow OR i.ChangedBy IS NULL THEN '' ELSE i.ChangedBy END FROM inserted i INNER JOIN deleted d ON d.MyTable_UID = i.MyTable_UID AND d.KeyID = i.KeyID WHERE (d.Col_F IS NULL AND i.Col_F IS NOT NULL) OR (d.Col_F IS NOT NULL AND i.Col_F IS NULL) OR d.Col_F != i.Col_F

The schema of the table having the trigger is similar to the following:

CREATE TABLE dbo.MyTable(
  MyTable_UID int identity(1,1),
  KeyID int,
  Col_A smallint, 
  Col_B int,
  Col_C varchar(50),
  Col_D varchar(100),
  Col_E varchar(25),
  Col_F varchar(50),
  Col_G varchar(100),
  CONSTRAINT PK_MyTable PRIMARY KEY(MyTable_UID, KeyID))

I was wondering if there was a way to make such a trigger more set based, rather than having multiple IF conditions, some of which triggers have many such IF conditions?
russellb
New Member
New Member

--
23 Jun 2011 06:01 AM
I would never do it this way. Now you're mixing different data types in the same column. I'd create an audit table for each base table you're auditing this way
rerichards
New Member
New Member

--
23 Jun 2011 06:11 AM
Thanks Russell. I will certainly consider that.

The main issue I am seeing has little to do with the single audit table (but like I said, I will explore your recommendation), but has more to do with the high number of IF UPDATE (Column_Name) statements. How does one make the potential UPDATES more set based?
russellb
New Member
New Member

--
23 Jun 2011 08:39 AM
I usually copy the entire "before" record to the audit table


Acceptable Use Policy
---