SQL Server Update Trigger, Get Only Modified Fields

SQL Server Update Trigger, Get Only modified fields

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

Find modified fields in Update Trigger

Unfortunately I'm not aware of any reliable built-in function that can give you an indication if the value of a column actually changed when an update statement fired a trigger.

However, there is pretty simple select statement you can execute to see what values have changed in what rows:

SELECT IIF(ISNULL(NULLIF(I.Col, D.Col), NULLIF(D.Col, I.Col)) IS NULL, 0, 1) As Col_Updated
FROM Inserted I
JOIN Deleted D
    ON I.PrimaryKey = D.PrimaryKey

The NULLIF function will return null if both columns are equal. If not, it returns the value of the first column.

The ISNULL function will return the first argument that is not null, or null if both arguments are null.

Using ISNULL on the result of two NULLIF functions where the column values are reversed will result in null if the values of both columns are the same, even if they both are null. If the values are different, even if one of them is null and the other is not, then ISNULL will return a value. Therefor, all you have to do is check if the ISNULL returned a value or null - if it return null, you know the column's value was not changed. If it returns a value, then it is changed.

Of course, for non-nullable columns you can simplify the condition like this:

SELECT IIF(I.Col <> D.Col, 1, 0) As Col_Updated
FROM...

However I would recommend against that, since if someone will change the column to allow null, the trigger code will have to change to support that - and in my experience, that's a bug waiting to happen - trigger code will probably not be changed, leading to false-negatives.

You can see a live demo on rextester.

Update

Another simple method you can use is this:

IIF((I.Col IS NULL AND D.Col IS NULL) OR (I.Col = D.Col), 0, 1) as IsUpdated.

This method relies on the fact that comparing null to anything else will result with unknown which is basically equivalent to false.

It's slightly more readable IMHO.

How to get names of the 'only updated columns' in a trigger?

Personally I hate triggers and try to avoid them most of the time ;-)

However, the only way I know of is with the function COLUMNS_UPDATED() inside your trigger code. See also examples like: https://sites.google.com/site/bugsarewelcome/home/columns_updated

SQL update trigger only when column is modified

You have two way for your question :

1- Use Update Command in your Trigger.

ALTER TRIGGER [dbo].[tr_SCHEDULE_Modified]
   ON [dbo].[SCHEDULE]
   AFTER UPDATE
AS BEGIN
    SET NOCOUNT ON;
    IF UPDATE (QtyToRepair) 
    BEGIN
        UPDATE SCHEDULE 
        SET modified = GETDATE()
           , ModifiedUser = SUSER_NAME()
           , ModifiedHost = HOST_NAME()
        FROM SCHEDULE S INNER JOIN Inserted I 
        ON S.OrderNo = I.OrderNo and S.PartNumber = I.PartNumber
        WHERE S.QtyToRepair <> I.QtyToRepair
    END 
END

2- Use Join between Inserted table and deleted table

ALTER TRIGGER [dbo].[tr_SCHEDULE_Modified]
   ON [dbo].[SCHEDULE]
   AFTER UPDATE
AS BEGIN
    SET NOCOUNT ON;    

    UPDATE SCHEDULE 
    SET modified = GETDATE()
       , ModifiedUser = SUSER_NAME()
       , ModifiedHost = HOST_NAME()
    FROM SCHEDULE S 
    INNER JOIN Inserted I ON S.OrderNo = I.OrderNo and S.PartNumber = I.PartNumber
    INNER JOIN Deleted D ON S.OrderNo = D.OrderNo and S.PartNumber = D.PartNumber                  
    WHERE S.QtyToRepair <> I.QtyToRepair
    AND D.QtyToRepair <> I.QtyToRepair
END

When you use update command for table SCHEDULE and Set QtyToRepair Column to new value, if new value equal to old value in one or multi row, solution 1 update all updated row in Schedule table but solution 2 update only schedule rows that old value not equal to new value.

How to log changed values in Update trigger

Here's one way using COLUMNS_UPDATED. Trigger does not depend on column names, so you can add or remove columns without problem. I have added some comments in the query

create trigger audit on Items
after update
as
begin
    set nocount on;
        create table #updatedCols (Id int identity(1, 1), updateCol nvarchar(200))

        --find all columns that were updated and write them to temp table
        insert into #updatedCols (updateCol)
        select
            column_name
        from
            information_schema.columns
        where   
            table_name = 'Items'   
            and convert(varbinary, reverse(columns_updated())) & power(convert(bigint, 2), ordinal_position - 1) > 0

        --temp tables are used because inserted and deleted tables are not available in dynamic SQL
        select * into #tempInserted from inserted
        select * into #tempDeleted from deleted

        declare @cnt int = 1
        declare @rowCnt int
        declare @columnName varchar(1000)
        declare @sql nvarchar(4000)

        select @rowCnt = count(*) from #updatedCols

        --execute insert statement for each updated column
        while @cnt <= @rowCnt
        begin
            select @columnName = updateCol from #updatedCols where id = @cnt

            set @sql = N'
                insert into [Events] ([RecordId], [EventTypeId], [EventDate], [ColumnName], [OriginalValue], [NewValue], [TenantId], [AppUserId], [TableName])
                select
                    i.Id, 2, GetUTCDate(), ''' + @columnName + ''', d.' + @columnName + ', i.' + @columnName +', i.TenantId, i.UpdatedBy, ''Item''
                from
                    #tempInserted i
                    join #tempDeleted d on i.Id = d.Id and isnull(Cast(i.' + @columnName + ' as varchar), '''') <> isnull(Cast(d.' +@columnName + ' as varchar), '''')
                '
            exec sp_executesql @sql
            set @cnt = @cnt + 1
        end
end

I have changed data type of TableName column of Events table to nvarchar.

SQLite modified trigger which only only updates modified field if values have actually changed

Instead of the operator != use the operator IS NOT which works when one or both of the operands are NULL:

......................................................
WHERE (OLD.order_id=NEW.order_id) 
  AND (
       (OLD.order_date IS NOT NEW.order_date) OR 
       (OLD.grand_total_in_dollars IS NOT NEW.grand_total_in_dollars) OR 
       (OLD.ship_to IS NOT NEW.ship_to) OR 
       (OLD.store IS NOT NEW.store) OR 
       (OLD.more_href IS NOT NEW.more_href) OR
       (OLD.grand_total IS NOT NEW.grand_total)
      );

Trigger for rows where specific column was changed

You need to compare the inserted and deleted tables, joining by primary key, and verify the results are different.

Note that the UPDATE() function only tells you if the column was present in the UPDATE, not if the value actually changed.

CREATE TRIGGER testTrigger
ON table
AFTER UPDATE
AS

  SET NOCOUNT ON;

  IF(UPDATE(KeyProperty))
  BEGIN
    UPDATE t
    SET Flag = True
    FROM (
        SELECT i.Id, i.KeyProperty
        FROM inserted i
        EXCEPT
        SELECT d.Id, d.KeyProperty
        FROM deleted d
    ) i
    JOIN table t ON t.Id = i.Id
  END

GO

---

Related Topics