Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- IF UPDATE([UserId]) AND EXISTS (SELECT [UserId] FROM inserted EXCEPT SELECT [UserId] FROM deleted)
- BEGIN
- UPDATE [MyTable] SET [UserIdModified] = SYSUTCDATETIME() FROM [inserted] WHERE [MyTable].[Id] = [inserted].[Id]
- END
- IF EXISTS (...)
- BEGIN
- ...
- END
- DROP TABLE IF EXISTS [MyTable]
- GO
- CREATE TABLE [MyTable] ([Id] INT IDENTITY NOT NULL PRIMARY KEY, [UserId] INT, [UserIdModified] DATETIME2)
- GO
- CREATE TRIGGER [trgMyTableUserIdModified]
- ON [MyTable]
- AFTER INSERT, UPDATE
- AS
- BEGIN
- IF UPDATE([UserId]) AND EXISTS (SELECT [UserId] FROM inserted EXCEPT SELECT [UserId] FROM deleted)
- BEGIN
- UPDATE [MyTable] SET [UserIdModified] = SYSUTCDATETIME() FROM [inserted] WHERE [MyTable].[Id] = [inserted].[Id]
- END
- END
- GO
- INSERT INTO [MyTable] ([UserId]) VALUES (1)
- SELECT * FROM [MyTable]
- WAITFOR DELAY '00:00:00.010'
- UPDATE [MyTable] SET [UserId] = 2
- SELECT *, 'Updated.' [Time should be] FROM [MyTable]
- WAITFOR DELAY '00:00:00.010'
- UPDATE [MyTable] SET [UserId] = 2
- SELECT *, 'Same as above.' [Time should be] FROM [MyTable]
- WAITFOR DELAY '00:00:00.010'
- UPDATE[MyTable] SET [UserId] = NULL
- SELECT *, 'Updated.' [Time should be] FROM [MyTable]
- WAITFOR DELAY '00:00:00.010'
- UPDATE [MyTable] SET [UserId] = NULL
- SELECT *, 'Same as above.' [Time should be] FROM [MyTable]
- WAITFOR DELAY '00:00:00.010'
- INSERT INTO [MyTable] ([UserId]) VALUES (1)
- SELECT *, 'Same as above for 1st element.' [Time should be] FROM [MyTable]
- WAITFOR DELAY '00:00:00.010'
- UPDATE [MyTable] SET [UserId] = 2 WHERE [Id] = 2
- SELECT *, 'Updated only for the 2nd element.' [Test] FROM [MyTable]
Add Comment
Please, Sign In to add comment