0
\$\begingroup\$

How can I improve the following code to track when an Azure SQL Database column value was last modified?

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

Does it make sense (from clarity, readability or performance point-of-view in your opinion) to include the (extra) UPDATE([UserId]) in the beginning of the IF clause, as opposed to only using EXISTS?

IF EXISTS (...)
BEGIN
...
END

UPDATE could clarify things out for a reader (e.g. the script will run only when [UserId] is included) but on the other hand it is redundant (perhaps without real benefits).

I built upon David Coster's answer from here.

Testing things out:

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]

The above test code prints out:

enter image description here

\$\endgroup\$
  • \$\begingroup\$ EXCEPT looks wrong, also I don't think you are following Coster's answer ( which looks sound ). \$\endgroup\$ – George Barwood Feb 16 at 13:29
  • 1
    \$\begingroup\$ @GeorgeBarwood What exactly looks wrong and how it could be improved? Added test code above so it is easy to try things out. \$\endgroup\$ – thomius Feb 16 at 14:04
  • \$\begingroup\$ I take it back, EXCEPT is fine, I didn't spot what you were doing. I think the IF UPDATE([UserId]) is good. \$\endgroup\$ – George Barwood Feb 16 at 14:22
  • \$\begingroup\$ @GeorgeBarwood Included UPDATE([UserId]) in the original proposal. \$\endgroup\$ – thomius Feb 16 at 14:38

Your Answer

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

Browse other questions tagged or ask your own question.