Guest User

Untitled

a guest
Feb 16th, 2019
94
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.56 KB | None | 0 0
  1. IF UPDATE([UserId]) AND EXISTS (SELECT [UserId] FROM inserted EXCEPT SELECT [UserId] FROM deleted)
  2. BEGIN
  3. UPDATE [MyTable] SET [UserIdModified] = SYSUTCDATETIME() FROM [inserted] WHERE [MyTable].[Id] = [inserted].[Id]
  4. END
  5.  
  6. IF EXISTS (...)
  7. BEGIN
  8. ...
  9. END
  10.  
  11. DROP TABLE IF EXISTS [MyTable]
  12. GO
  13. CREATE TABLE [MyTable] ([Id] INT IDENTITY NOT NULL PRIMARY KEY, [UserId] INT, [UserIdModified] DATETIME2)
  14. GO
  15. CREATE TRIGGER [trgMyTableUserIdModified]
  16. ON [MyTable]
  17. AFTER INSERT, UPDATE
  18. AS
  19. BEGIN
  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
  24. END
  25. GO
  26. INSERT INTO [MyTable] ([UserId]) VALUES (1)
  27. SELECT * FROM [MyTable]
  28.  
  29. WAITFOR DELAY '00:00:00.010'
  30. UPDATE [MyTable] SET [UserId] = 2
  31. SELECT *, 'Updated.' [Time should be] FROM [MyTable]
  32.  
  33. WAITFOR DELAY '00:00:00.010'
  34. UPDATE [MyTable] SET [UserId] = 2
  35. SELECT *, 'Same as above.' [Time should be] FROM [MyTable]
  36.  
  37. WAITFOR DELAY '00:00:00.010'
  38. UPDATE[MyTable] SET [UserId] = NULL
  39. SELECT *, 'Updated.' [Time should be] FROM [MyTable]
  40.  
  41. WAITFOR DELAY '00:00:00.010'
  42. UPDATE [MyTable] SET [UserId] = NULL
  43. SELECT *, 'Same as above.' [Time should be] FROM [MyTable]
  44.  
  45. WAITFOR DELAY '00:00:00.010'
  46. INSERT INTO [MyTable] ([UserId]) VALUES (1)
  47. SELECT *, 'Same as above for 1st element.' [Time should be] FROM [MyTable]
  48.  
  49. WAITFOR DELAY '00:00:00.010'
  50. UPDATE [MyTable] SET [UserId] = 2 WHERE [Id] = 2
  51. SELECT *, 'Updated only for the 2nd element.' [Test] FROM [MyTable]
Add Comment
Please, Sign In to add comment