title: UPDATE modified date trigger only if modified date is not supplied
body: <p>I have some tables that have "Created" and "Modified" dates on them. I'd like to be able to update the latter ONLY if the modified date was not supplied by the application layer. We need to have the application layer be able to set the modified date to a specific value in the event that an offline transaction (such as a device with no internet connection syncs up at a later date) occurs, but we can't always ensure that ever application developer will always remember to set the modified date in the application code.</p>
  
  <p>Given the following table:</p>
  
  <pre><code>CREATE TABLE [dbo].[Tests](
      [TestID] [int] IDENTITY(1,1) NOT NULL,
      [Name] [nvarchar](50) NOT NULL,
      [CreatedDate] [datetime2](7) NOT NULL,
      [ModifiedDate] [datetime2](7) NOT NULL,
   CONSTRAINT [PK_Tests] PRIMARY KEY CLUSTERED 
  (
      [TestID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
  ) ON [PRIMARY]
  
  GO
  
  ALTER TABLE [dbo].[Tests] ADD  CONSTRAINT [DF_Tests_CreatedDate]  DEFAULT (getutcdate()) FOR [CreatedDate]
  GO
  
  ALTER TABLE [dbo].[Tests] ADD  CONSTRAINT [DF_Tests_ModifiedDate]  DEFAULT (getutcdate()) FOR [ModifiedDate]
  GO
  </code></pre>
  
  <p>It would seem that when no "modified date" is supplied in the update, instead of either updating or throwing an exception, simply stays the same.</p>
  
  <p>Example Test Cases:</p>
  
  <p>Generated LINQ-to-Entities code:</p>
  
  <pre><code>exec sp_executesql N'update [dbo].[Tests]
  set [Name] = @0
  where ([TestID] = @1)
  ',N'@0 nvarchar(50),@1 int',@0=N'Test_635267931494843908',@1=3
  </code></pre>
  
  <p><em>should update modified date to GETUTCDATE()</em></p>
  
  <p>SQL code: </p>
  
  <pre><code>UPDATE Tests
  SET Name = 'Test' + CONVERT(nvarchar(255), NEWID()) 
  WHERE TestID =3
  </code></pre>
  
  <p><em>should update modified date to GETUTCDATE()</em></p>
  
  <p>SQL Code with modified date:</p>
  
  <pre><code>UPDATE Tests
  SET Name = 'Test' + CONVERT(nvarchar(255), NEWID()),
  ModifiedDate = '2014-01-31 19:10:48'
  WHERE TestID =3
  </code></pre>
  
  <p><em>should update modified date to '2014-01-31 19:10:48'</em></p>
  
  <p>I have tried the following: </p>
  
  <pre><code>CREATE TRIGGER 
      [dbo].[ModifiedDateUpdateTrigger]
  ON 
      [dbo].[Tests]
  AFTER UPDATE
  AS 
  BEGIN
  
      UPDATE dbo.Tests 
      SET ModifiedDate = GETUTCDATE()
      FROM dbo.Tests as t
      INNER JOIN inserted as i on i.TestID = t.TestID
      INNER JOIN deleted as d on d.TestID = t.TestID
      WHERE t.ModifiedDate &lt;&gt; i.ModifiedDate
          AND d.ModifiedDate &lt;&gt; i.ModifiedDate
  END
  </code></pre>
  
  <p>and</p>
  
  <pre><code>CREATE TRIGGER 
      [dbo].[ModifiedDateUpdateTrigger]
  ON 
      [dbo].[Tests]
  FOR UPDATE
  AS 
  BEGIN
      DECLARE @m datetime2(7) = (select MODIFIEDDATE FROM inserted)
      print @m
      IF (select MODIFIEDDATE FROM inserted)  IS  NULL
      BEGIN
          RETURN
      END
  
      UPDATE dbo.Tests 
      SET ModifiedDate = GETUTCDATE()
      FROM INSERTED i
      WHERE i.TestID = Tests.TestID
  END
  </code></pre>
  
  <p>and finally:</p>
  
  <pre><code>CREATE TRIGGER 
      [dbo].[ModifiedDateUpdateTrigger]
  ON 
      [dbo].[Tests]
  FOR UPDATE
  AS 
  BEGIN
      DECLARE @m datetime2(7) = (select MODIFIEDDATE FROM inserted)
      print @m
      IF (select MODIFIEDDATE FROM inserted) &lt;&gt; (SELECT i.ModifiedDate FROM Tests as t INNER JOIN inserted as i on t.TestID = i.TestID)
      BEGIN
          RETURN
      END
  
      UPDATE dbo.Tests 
      SET ModifiedDate = GETUTCDATE()
      FROM INSERTED i
      WHERE i.TestID = Tests.TestID
  END
  </code></pre>
  
  <p>None of these work for all the desire test case.</p>