sqlsql-servertriggerssql-updatedatabase-replication

SQL Server Update Trigger, Get Only modified fields


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)

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

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

something like following...

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

Solution

  • 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
    

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

    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