SQL Server 更新触发器，只获取修改的字段

SQL Server Update Trigger, Get Only modified fields(SQL Server 更新触发器，只获取修改的字段)

问题描述

我知道 COLUMNS_UPDATED，我需要一些快速的捷径(如果有人做过，我已经在做一个，但如果有人能节省我的时间，我会感谢它)

I am aware of COLUMNS_UPDATED, well I need some quick shortcut (if anyone has made, I am already making one, but if anyone can save my time, I will appriciate it)

我基本上需要一个只包含更新列值的 XML，我需要这个用于复制目的.

I need basicaly an XML of only updated column values, I need this for replication purpose.

SELECT * FROM inserted 给我每一列，但我只需要更新的.

SELECT * FROM inserted gives me each column, but I need only updated ones.

类似跟随...

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() 来获取更新的值

Inside the trigger, you can use COLUMNS_UPDATED() like this in order to get updated value

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

But this snipet of code fails when you have a table with more than 62 columns.. Arth.Overflow...

这是处理超过 62 列的最终版本，但只给出了更新列的数量.很容易与syscolumns"链接以获取名称

Here is the final version which handles more than 62 columns but give only the number of the updated columns. It's easy to link with 'syscolumns' to get the name

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

这篇关于SQL Server 更新触发器，只获取修改的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！