我知道COLUMNS_UPDATED,我需要一些快捷的快捷方式(如果有人做过,我已经做了一个,但是如果有人可以节省我的时间,我会加以评价)
我需要基本的只有更新的列值的XML,我需要这个复制目的.
SELECT * FROM inserted给了我每列,但我只需要更新的列.
像以下的东西
CREATE TRIGGER DBCustomers_Insert
ON DBCustomers
AFTER UPDATE
AS
BEGIN
DECLARE @sql as NVARCHAR(1024);
SET @sql = 'SELECT ';
I NEED HELP FOR FOLLOWING LINE ....,I can manually write every column,but I need
an automated routin which can work regardless of column specification
for each column,if its modified append $sql = ',' + columnname...
SET @sql = $sql + ' FROM inserted FOR XML RAW';
DECLARE @x as XML;
SET @x = CAST(EXEC(@sql) AS XML);
.. use @x
END
解决方法
在触发器内部,您可以使用这样的COLUMNS_UPDATED()来获取更新的值
-- Get the table id of the trigger -- DECLARE @idTable INT SELECT @idTable = T.id FROM sysobjects P JOIN sysobjects T ON P.parent_obj = T.id WHERE P.id = @@procid -- Get COLUMNS_UPDATED if update -- DECLARE @Columns_Updated VARCHAR(50) SELECT @Columns_Updated = ISNULL(@Columns_Updated + ',','') + name FROM syscolumns WHERE id = @idTable AND CONVERT(VARBINARY,REVERSE(COLUMNS_UPDATED())) & POWER(CONVERT(BIGINT,2),colorder - 1) > 0
但是当您有一个具有超过62列的表时,这个代码的代码会失败.Arth.Overflow …
这是处理超过62列但只提供更新列数的最终版本.很容易与’syscolumns’链接来获取名称
DECLARE @Columns_Updated VARCHAR(100)
SET @Columns_Updated = ''
DECLARE @maxByteCU INT
DECLARE @curByteCU INT
SELECT @maxByteCU = DATALENGTH(COLUMNS_UPDATED()),@curByteCU = 1
WHILE @curByteCU <= @maxByteCU BEGIN
DECLARE @cByte INT
SET @cByte = SUBSTRING(COLUMNS_UPDATED(),@curByteCU,1)
DECLARE @curBit INT
DECLARE @maxBit INT
SELECT @curBit = 1,@maxBit = 8
WHILE @curBit <= @maxBit BEGIN
IF CONVERT(BIT,@cByte & POWER(2,@curBit - 1)) <> 0
SET @Columns_Updated = @Columns_Updated + '[' + CONVERT(VARCHAR,8 * (@curByteCU - 1) + @curBit) + ']'
SET @curBit = @curBit + 1
END
SET @curByteCU = @curByteCU + 1
END
