SQL Server比较字段值及其默认值

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

我有我需要的一切,但我不知道如何比较2.因为我没有字段名称作为常量,只有在一个变量中,我看不出如何从'插入'table(记住我在触发器中)以查看它是否与默认值相同(现在在@ColDefault中作为varchar保存)。

2 个解决方案

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

那么我在MyTableWithTrigger上的触发器将需要处理多个记录。 “插入”的伪记录中不仅包含一条记录。

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

话虽如此,为了比较数据,你可以像这样运行一个select语句:

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.

这将返回插入的pseudotable中与默认值不匹配的所有行。听起来你想对这些行做些什么,所以你可能要做的是在调用@sqlToExec字符串之前创建一个临时表,而不是仅仅选择数据,将其插入到临时表中。然后,您可以使用这些行来执行您需要的任何异常处理。

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.

一个问题 - 这个T-SQL仅适用于数字字段。您可能希望为不同类型的字段构建单独的处理。你可能有varchars,数字,blob等,你需要不同的方法来比较它们。

#2

I suspect you can do this using and exec.

我怀疑你可以使用和exec这样做。

But why not just code generate once. It will be more performant

但为什么不只是代码生成一次。它会更高效

#1