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

我知道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@H_502_9@

解决方法

在触发器内部,您可以使用这样的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@H_502_9@ 
 但是当您有一个具有超过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@H_502_9@

原文链接：https://www.f2er.com/mssql/81709.html

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

解决方法

猜你在找的MsSQL相关文章