UPDATE modified date trigger only if modified date is not supplied

sql-server, triggers

Solution

You can't use any of these methods that pretend that `inserted` could only ever contain one row (what if I update more than one test in a single statement?). Instead, you need to perform a join-based update, and you can just use `COALESCE()` to determine whether a different value was supplied for that column (when it hasn't, replace it with `GETUTCDATE()`). Here is the approach I prefer:

CREATE TRIGGER  [dbo].[ModifiedDateUpdateTrigger]
ON [dbo].[Tests]
FOR UPDATE
AS 
BEGIN
  SET NOCOUNT ON;

  UPDATE t
    SET ModifiedDate = COALESCE(NULLIF(i.ModifiedDate,d.ModifiedDate), GETUTCDATE())
    FROM dbo.Tests AS t
    INNER JOIN inserted AS i
     ON t.TestID = i.TestID
    INNER JOIN deleted AS d
     ON t.TestID = d.TestID;
END
GO

The reason for the extra complication is this. Consider a simple table:

CREATE TABLE dbo.Tests(TestID INT, ModifiedDate DATETIME, x VARCHAR(1));

Now, two rows:

INSERT dbo.Tests(TestID) SELECT 1,GETDATE() UNION ALL SELECT 2,GETDATE();

We want to update the `ModifiedDate` column in this scenario:

UPDATE dbo.Tests SET x = 'y';

But also in this scenario:

UPDATE dbo.Tests SET x = 'y', ModifiedDate = CASE
  WHEN TestID = 1 THEN '19000101' ELSE ModifiedDate END
  WHERE TestID IN (1,2);

Both of these are handled adequately as multi-row updates from the trigger, but the latter might be handled incorrectly if you just assume that a value supplied in `inserted` is a new one. You need to compare it to `deleted` to ensure that it has actually changed. The only thing this makes it hard to do? Set the value explicitly to `NULL` (well, without disabling the trigger). :-) In this case it will actually replace the `NULL` you passed with `GETUTCDATE()`. Not that you'd ever want to do that, but I didn't want to leave it unsaid.

update

In the comments you mentioned one case where you want to prevent the ModifiedDate from getting "bumped" if you hard-coded the same ModifiedDate value in subsequent UPDATE statements. I couldn't find a way of differentiating in an after trigger (since the table has already been changed, and at that point it's impossible to tell if that was from the current update or a previous one), but I did find a way to differentiate in an instead of trigger. So if you can change to an instead of trigger, you can do this - the only additional complication is that you have to also account for any other columns that might not be mentioned explicitly in the update statement or that you want to handle differently depending on before/after values. Here is the trigger:

CREATE TRIGGER  [dbo].[ModifiedDateUpdateTrigger]
ON [dbo].[Tests]
INSTEAD OF UPDATE
AS 
BEGIN
  SET NOCOUNT ON;

  UPDATE src 
    SET src.Name = i.Name, /* other columns that might get updated */
      src.ModifiedDate = CASE 
        WHEN i.ModifiedDate <> src.ModifiedDate THEN CASE 
          WHEN UPDATE(ModifiedDate) THEN i.ModifiedDate ELSE GETUTCDATE() END
        WHEN i.ModifiedDate = src.ModifiedDate THEN CASE 
          WHEN NOT UPDATE(ModifiedDate) THEN GETUTCDATE() ELSE src.ModifiedDate END
        ELSE GETUTCDATE() 
      END
    FROM dbo.Tests AS src
    INNER JOIN inserted AS i
    ON i.TestID = src.TestID
    INNER JOIN deleted AS d
    ON i.TestID = d.TestID;
END
GO

And here is a demo: http://sqlfiddle.com/#!3/4a00b5/1

I'm not 100% confident that the `ELSE` condition is required, but exhausted all the time I could invest in testing for today.

Problem

Original source