NFzeb6vX

1IF UPDATE([UserId]) AND EXISTS (SELECT [UserId] FROM inserted EXCEPT SELECT [UserId] FROM deleted)
2BEGIN
3    UPDATE [MyTable] SET [UserIdModified] = SYSUTCDATETIME() FROM [inserted] WHERE [MyTable].[Id] = [inserted].[Id]
4END
5	
6IF EXISTS (...)
7BEGIN
8...
9END
10	
11DROP TABLE IF EXISTS [MyTable]
12GO
13CREATE TABLE [MyTable] ([Id] INT IDENTITY NOT NULL PRIMARY KEY, [UserId] INT, [UserIdModified] DATETIME2)
14GO
15CREATE TRIGGER [trgMyTableUserIdModified] 
16ON [MyTable]
17AFTER INSERT, UPDATE
18AS
19BEGIN
20    IF UPDATE([UserId]) AND EXISTS (SELECT [UserId] FROM inserted EXCEPT SELECT [UserId] FROM deleted)
21    BEGIN
22        UPDATE [MyTable] SET [UserIdModified] = SYSUTCDATETIME() FROM [inserted] WHERE [MyTable].[Id] = [inserted].[Id]
23    END
24END
25GO
26INSERT INTO [MyTable] ([UserId]) VALUES (1)
27SELECT * FROM [MyTable]
28
29WAITFOR DELAY '00:00:00.010'
30UPDATE [MyTable] SET [UserId] = 2
31SELECT *, 'Updated.' [Time should be] FROM [MyTable]
32
33WAITFOR DELAY '00:00:00.010'
34UPDATE [MyTable] SET [UserId] = 2
35SELECT *, 'Same as above.' [Time should be] FROM [MyTable]
36
37WAITFOR DELAY '00:00:00.010'
38UPDATE[MyTable] SET [UserId] = NULL
39SELECT *, 'Updated.' [Time should be] FROM [MyTable]
40
41WAITFOR DELAY '00:00:00.010'
42UPDATE [MyTable] SET [UserId] = NULL
43SELECT *, 'Same as above.' [Time should be] FROM [MyTable]
44
45WAITFOR DELAY '00:00:00.010'
46INSERT INTO [MyTable] ([UserId]) VALUES (1)
47SELECT *, 'Same as above for 1st element.' [Time should be] FROM [MyTable]
48
49WAITFOR DELAY '00:00:00.010'
50UPDATE [MyTable] SET [UserId] = 2 WHERE [Id] = 2
51SELECT *, 'Updated only for the 2nd element.' [Test] FROM [MyTable]