SQL Server Compare field value with its default

Question

I need to iterate through the fields on a table and do something if its value does not equal its default value.

I'm in a trigger and so I know the table name. I then loop through each of the fields using this loop:

select @field = 0, @maxfield = max(ORDINAL_POSITION) from
INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = @TableName

while @field < @maxfield
begin
...

I can then get the field name on each iteration through the loop:

select @fieldname = COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = @TableName
and ORDINAL_POSITION = @field

And I can get the default value for that column:

select @ColDefault = SUBSTRING(Column_Default,2,LEN(Column_Default)-2)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Table_Name = @TableName
AND Column_name = @fieldname

I have everything I need but I can't see how to then compare the 2. Because I don't have the field name as a constant, only in a variable, I can't see how to get the value out of the 'inserted' table (remember I'm in a trigger) in order to see if it is the same as the default value (held now in @ColDefault as a varchar).

Answer 1

First, remember that a trigger can be fired with multiple records coming in simultaneously. If I do this:

INSERT INTO dbo.MyTableWithTrigger
  SELECT * FROM dbo.MyOtherTable

then my trigger on the MyTableWithTrigger will need to handle more than one record. The "inserted" pseudotable will have more than just one record in it.

Having said that, to compare the data, you can run a select statement like this:

DECLARE @sqlToExec VARCHAR(8000)
SET @sqlToExec = 'SELECT * FROM INSERTED WHERE [' + @fieldname + '] <> ' + @ColDefault
EXEC(sqlToExec)

That will return all rows from the inserted pseudotable that don't match the defaults. It sounds like you want to DO something with those rows, so what you might want to do is create a temp table before you call that @sqlToExec string, and instead of just selecting the data, insert it into the temp table. Then you can use those rows to do whatever exception handling you need.

One catch - this T-SQL only works for numeric fields. You'll probably want to build separate handling for different types of fields. You might have varchars, numerics, blobs, etc., and you'll need different ways of comparing those.