问题:

If within a Sql Server trigger I want to test if a column was not included in the UPDATE statement, is

IF NOT UPDATE(column)

the correct syntax? I've read a much older discussion suggesting this might not also work and one should use an IF UPDATE(column)...ELSE syntax, except I would not have any relevant statement to add for the first condition.

回答1:

The syntax IF NOT UPDATE(column) is correct and can be verified by the following simple test, as @NoDisplayName pointed out:

CREATE TABLE dbo.Test(Id int, Foo int);
GO
INSERT INTO dbo.Test VALUES (1, 1);
GO

CREATE TRIGGER TestTrigger ON dbo.Test
AFTER UPDATE
AS
IF NOT UPDATE(Foo)
  PRINT 'Foo Not Included In Update Statement';
IF UPDATE(Id)
  PRINT 'Id Included In Update Statement';
GO

UPDATE dbo.Test SET Id = 1;

/*
output:
Foo Not Included In Update Statement
Id Included In Update Statement
*/

回答2:

I don't have a server to check this on right now, but this is how you can list the rows in an update where "columnToCheck" was not changed.

CREATE TRIGGER trgMyTrigger ON [dbo].[myTable] 
AFTER UPDATE
AS

    SELECT
    *
    FROM inserted AS i
    INNER JOIN deleted AS d ON i.ID = d.ID
    WHERE i.columnToCheck = d.columnToCheck

GO