This is a difficult situation. The trigger you have created is trying to deal with a situation that I find to be very painful and not very useful in the long run. This Audit table will hold values for a given column along with the date it was modified. Here is the real issue with this type of design. Consider being asked to figure out all the values for a given primary key value on a given date. Think about how difficult this is going to be when you have a table with only 10 o 15 columns. You are going to have to write a big ugly nasty query just to determine the values for each column. Consider a much wider table and how difficult that is going to be.

Now lets examine the actual trigger. There are some major issues with this trigger. Performance is going to be a serious issue here because of the complexity of what you are doing. You have built a while loop inside this trigger to insert a new row into the audit table for each column in CER_SEDA_DOMAIN_A. Looping in t-sql is almost always very slow.

If this was a system I was working with I would consider rethinking the audit structure here and this can become a pretty simple process.

For anybody else stumbling in here I formatted the posted code so we can see it easier.

ALTER TRIGGER tr_trigtest ON dbo.CER_SEDA_DOMAIN_A

FOR INSERT

,UPDATE

,DELETE

AS

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)

SELECT @TableName = 'dbo.CER_SEDA_DOMAIN_A'

-- date and user

SELECT @UserName = system_user

,@UpdateDate = convert(VARCHAR(8), getdate(), 112) + ' ' + convert(VARCHAR(12), getdate(), 114)

-- Action

IF EXISTS (

SELECT *

FROM inserted

)

IF EXISTS (

SELECT *

FROM deleted

)

SELECT @Type = 'U'

ELSE

SELECT @Type = 'I'

ELSE

SELECT @Type = 'D'

-- get list of columns

SELECT *

INTO #ins

FROM inserted

SELECT *

INTO #del

FROM deleted

-- 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

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 @fieldname = COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = @TableName

AND ORDINAL_POSITION = @field

SELECT @sql = 'insert CMMonitor.[dbo].Audit (Type, TableName, PK, FieldName, OldValue, NewValue, UpdateDate, UserName)'

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 + ' 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

GO