SQL Server更新触发器,仅获取修改的字段

我需要基本的只有更新的列值的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

解决方法

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!