12

I have table with 42 columns and a trigger which should do some stuff when 38 of these columns are updated. So, I need to skip the logic if the rest 4 columns are changed.

I can use UPDATE() function and create one big IF condition, but prefer to do something shorter. Using COLUMNS_UPDATED I can check if all of certain columns are updated?

For example, checking if column 3, 5 and 9 are updated:

  IF 
  (
    (SUBSTRING(COLUMNS_UPDATED(),1,1) & 20 = 20)
     AND 
    (SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1) 
  )
    PRINT 'Columns 3, 5 and 9 updated';

enter image description here

So, value 20 for column 3 and 5, and value 1 for column 9 because it is set in the first bit of the second byte. If I change the statement to OR it will check if columns 3 and 5 or column 9 is/are updated?

How can apply OR logic in the context of one byte?

  • 6
    Well, do you want to know if those columns are mentioned in the SET list, or if the values actually changed? Both UPDATE and COLUMNS_UPDATED() only tell you the former. If you want to know if the values actually changed, you'll need to do a proper comparison of inserted and deleted. – Aaron Bertrand Mar 17 '16 at 16:18
  • Instead of using SUBSTRING to split the value returned form COLUMNS_UPDATED(), you should use a bitwise comparison, as shown in the documentation. Beware that if you change the table in any way, the order of values returned by COLUMNS_UPDATED() will change. – Max Vernon Mar 17 '16 at 16:32
  • As @AaronBertrand aluded to, if you need to see values that were changed even though they weren't explicitly updated using a SET or UPDATE statement, you may want to look at using CHECKSUM() or BINARY_CHECKSUM(), or even HASHBYTES() over the columns in question. – Max Vernon Mar 17 '16 at 16:36
17

You can use CHECKSUM() as a fairly simple methodology for comparing actual values to see if they were changed. CHECKSUM() will generate a checksum across a list of passed-in values, of which the number and type are indeterminate. Beware, there is a small chance comparing checksums like this will result in false negatives. If you cannot deal with that, you can use HASHBYTES instead1.

The example below uses an AFTER UPDATE trigger to retain a history of modifications made to the TriggerTest table only if either of the values in the Data1 or Data2 columns change. If Data3 changes, no action is taken.

USE tempdb;
IF COALESCE(OBJECT_ID('dbo.TriggerTest'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerTest;
END
CREATE TABLE dbo.TriggerTest
(
    TriggerTestID INT NOT NULL
        CONSTRAINT PK_TriggerTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Data1 VARCHAR(10) NULL
    , Data2 VARCHAR(10) NOT NULL
    , Data3 DATETIME NOT NULL
);

IF COALESCE(OBJECT_ID('dbo.TriggerResult'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerResult;
END
CREATE TABLE dbo.TriggerResult
(
    TriggerTestID INT NOT NULL
    , Data1OldVal VARCHAR(10) NULL
    , Data1NewVal VARCHAR(10) NULL
    , Data2OldVal VARCHAR(10) NULL
    , Data2NewVal VARCHAR(10) NULL
);

GO
IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0 
BEGIN
    DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS 
BEGIN
    INSERT INTO TriggerResult
    (
        TriggerTestID
        , Data1OldVal
        , Data1NewVal
        , Data2OldVal
        , Data2NewVal
    )
    SELECT d.TriggerTestID
        , d.Data1
        , i.Data1
        , d.Data2
        , i.Data2
    FROM inserted i 
        LEFT JOIN deleted d ON i.TriggerTestID = d.TriggerTestID
    WHERE CHECKSUM(i.Data1, i.Data2) <> CHECKSUM(d.Data1, d.Data2);
END
GO

INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
VALUES ('blah', 'foo', GETDATE());

UPDATE dbo.TriggerTest 
SET Data1 = 'blah', Data2 = 'fee' 
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult

enter image description here

If you're insistent on using the COLUMNS_UPDATED() function, you should not hard-code the ordinal value of the columns in question, since the table definition may change, which may invalidate hard-coded value(s). You can calculate what the value should be at runtime using the system tables. Be aware that the COLUMNS_UPDATED() function returns true for the given column bit if the column is modified in ANY row affected by the UPDATE TABLE statement.

USE tempdb;
IF COALESCE(OBJECT_ID('dbo.TriggerTest'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerTest;
END
CREATE TABLE dbo.TriggerTest
(
    TriggerTestID INT NOT NULL
        CONSTRAINT PK_TriggerTest
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Data1 VARCHAR(10) NULL
    , Data2 VARCHAR(10) NOT NULL
    , Data3 DATETIME NOT NULL
);

IF COALESCE(OBJECT_ID('dbo.TriggerResult'), 0) <> 0
BEGIN
    DROP TABLE dbo.TriggerResult;
END
CREATE TABLE dbo.TriggerResult
(
    TriggerTestID INT NOT NULL
    , Data1OldVal VARCHAR(10) NULL
    , Data1NewVal VARCHAR(10) NULL
    , Data2OldVal VARCHAR(10) NULL
    , Data2NewVal VARCHAR(10) NULL
);

GO
IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0 
BEGIN
    DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS 
BEGIN
    DECLARE @ColumnOrdinalTotal INT = 0;

    SELECT @ColumnOrdinalTotal = @ColumnOrdinalTotal 
        + POWER (
                2 
                , COLUMNPROPERTY(t.object_id,c.name,'ColumnID') - 1
            )
    FROM sys.schemas s
        INNER JOIN sys.tables t ON s.schema_id = t.schema_id
        INNER JOIN sys.columns c ON t.object_id = c.object_id
    WHERE s.name = 'dbo'
        AND t.name = 'TriggerTest'
        AND c.name IN (
            'Data1'
            , 'Data2'
        );

    IF (COLUMNS_UPDATED() & @ColumnOrdinalTotal) > 0
    BEGIN
        INSERT INTO TriggerResult
        (
            TriggerTestID
            , Data1OldVal
            , Data1NewVal
            , Data2OldVal
            , Data2NewVal
        )
        SELECT d.TriggerTestID
            , d.Data1
            , i.Data1
            , d.Data2
            , i.Data2
        FROM inserted i 
            LEFT JOIN deleted d ON i.TriggerTestID = d.TriggerTestID;
    END
END
GO

--this won't result in rows being inserted into the history table
INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
VALUES ('blah', 'foo', GETDATE());

SELECT *
FROM dbo.TriggerResult;

enter image description here

--this will insert rows into the history table
UPDATE dbo.TriggerTest 
SET Data1 = 'blah', Data2 = 'fee' 
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult;

enter image description here

--this WON'T insert rows into the history table
UPDATE dbo.TriggerTest 
SET Data3 = GETDATE()
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult

enter image description here

--this will insert rows into the history table, even though only
--one of the columns was updated
UPDATE dbo.TriggerTest 
SET Data1 = 'blum' 
WHERE TriggerTestID = 1;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult;

enter image description here

This demo inserts rows into the history table that perhaps should not be inserted. The rows have had their Data1 column updated for some rows, and have had the Data3 column updated for some rows. Since this is a single statement, all rows are processed by a single pass through the trigger. Since some rows have Data1 updated, which is part of the COLUMNS_UPDATED() comparison, all rows seen by the trigger are inserted into the TriggerHistory table. If this is "incorrect" for your scenario, you may need to handle each row separately, using a cursor.

INSERT INTO dbo.TriggerTest (Data1, Data2, Data3)
SELECT TOP(10) LEFT(o.name, 10)
    , LEFT(o1.name, 10)
    , GETDATE()
FROM sys.objects o
    , sys.objects o1;

UPDATE dbo.TriggerTest 
SET Data1 = CASE WHEN TriggerTestID % 6 = 1 THEN Data2 ELSE Data1 END
    , Data3 = CASE WHEN TriggerTestID % 6 = 2 THEN GETDATE() ELSE Data3 END;

SELECT *
FROM dbo.TriggerTest;

SELECT *
FROM dbo.TriggerResult;

The TriggerResult table now has some potentially misleading rows that look like they don't belong since they show absolutely no changes (to the two columns in that table). In the 2nd set of rows in the image below, TriggerTestID 7 is the only one that looks like it was modified. The other rows only had the Data3 column updated; however since the one row in the batch had Data1 updated, all rows are inserted in the TriggerResult table.

enter image description here

Alternately, as @AaronBertrand and @srutzky pointed out, you can perform an comparison of the actual data in the inserted and deleted virtual tables. Since the structure of both tables is identical, you can use an EXCEPT clause in the trigger to capture rows where the precise columns you are interested in have changed:

IF COALESCE(OBJECT_ID('dbo.TriggerTest_AfterUpdate'), 0) <> 0 
BEGIN
    DROP TRIGGER TriggerTest_AfterUpdate;
END
GO
CREATE TRIGGER TriggerTest_AfterUpdate
ON dbo.TriggerTest
AFTER UPDATE
AS 
BEGIN
    ;WITH src AS
    (
        SELECT d.TriggerTestID
            , d.Data1
            , d.Data2
        FROM deleted d
        EXCEPT 
        SELECT i.TriggerTestID
            , i.Data1
            , i.Data2
        FROM inserted i
    )
    INSERT INTO dbo.TriggerResult 
    (
        TriggerTestID, 
        Data1OldVal, 
        Data1NewVal, 
        Data2OldVal, 
        Data2NewVal
    )
    SELECT i.TriggerTestID
        , d.Data1
        , i.Data1
        , d.Data2
        , i.Data2
    FROM inserted i 
        INNER JOIN deleted d ON i.TriggerTestID = d.TriggerTestID
END
GO

1 - see https://stackoverflow.com/questions/297960/hash-collision-what-are-the-chances for a discsussion of the vanishingly small chance that the HASHBYTES calculation may also result in collisions. Preshing has a decent analysis of this problem as well.

  • 2
    This is good info, but "If you cannot deal with that, you can use HASHBYTES instead." is misleading. It's true that HASHBYTES is less likely to have a false negatives than CHECKSUM (likelihood varying on size of algorithm used), but it cannot be ruled out. Any hashing function will always have the potential to have collisions since it is quite likely to be reduced information. The only way to be certain of no changes is to compare the INSERTED and DELETED tables, and using a _BIN2 collation if it is string data. Comparing hashes only gives certainty for differences. – Solomon Rutzky Mar 17 '16 at 22:30
  • 2
    @srutzky If we're going to be worried about collisions let's also state the likelihood thereof. stackoverflow.com/questions/297960/… – Dave Mar 17 '16 at 22:45
  • 1
    @Dave I'm not saying don't use hashes: use them to identify items that have changed. My point is, since the likelihood > 0%, it should be stated rather than implied that it's guaranteed (the current wording that I quoted) so readers understand it better. Yes, the probability of a collision is very, very small, but not zero, and varies by size of source data. If I need to guarantee that two values are the same, I will spend a few extra CPU cycles to check. Depending on the hash size, there might not be much perf difference between the hash and a BIN2 compare, so go for the 100% accurate one. – Solomon Rutzky Mar 18 '16 at 5:31
  • 1
    Thanks for putting in that footnote (+1). Personally, I would use a resource other than that particular answer as it is overly simplistic. There are two issues: 1) as the source value sizes get bigger, the probability increases. I read through several posts on S.O. and other sites last night, and one person using this on images reported collisions after 25,000 entries, and 2) probability is just that, relative risk, there is nothing to say that someone using a hash won't run into collisions a few times in 10k entries. Chance = luck. It's ok to rely on if you are aware of it being luck ;-). – Solomon Rutzky Mar 18 '16 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.