sql server - UPDATE modified date trigger only if modified date is not supplied -

i have tables have "created" , "modified" dates on them. i'd able update latter if modified date not supplied application layer. need have application layer able set modified date specific value in event offline transaction (such device no internet connection syncs @ later date) occurs, can't ensure ever application developer remember set modified date in application code.

given following table:

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()) [createddate] go  alter table [dbo].[tests] add  constraint [df_tests_modifieddate]  default (getutcdate()) [modifieddate] go 

it seem when no "modified date" supplied in update, instead of either updating or throwing exception, stays same.

example test cases:

generated linq-to-entities code:

exec sp_executesql n'update [dbo].[tests] set [name] = @0 ([testid] = @1) ',n'@0 nvarchar(50),@1 int',@0=n'test_635267931494843908',@1=3 

should update modified date getutcdate()

sql code:

update tests set name = 'test' + convert(nvarchar(255), newid())  testid =3 

should update modified date getutcdate()

sql code modified date:

update tests set name = 'test' + convert(nvarchar(255), newid()), modifieddate = '2014-01-31 19:10:48' testid =3 

should update modified date '2014-01-31 19:10:48'

i have tried following:

create trigger      [dbo].[modifieddateupdatetrigger] on      [dbo].[tests] after update  begin      update dbo.tests      set modifieddate = getutcdate()     dbo.tests t     inner join inserted on i.testid = t.testid     inner join deleted d on d.testid = t.testid     t.modifieddate <> i.modifieddate         , d.modifieddate <> i.modifieddate end 

and

create trigger      [dbo].[modifieddateupdatetrigger] on      [dbo].[tests] update  begin     declare @m datetime2(7) = (select modifieddate inserted)     print @m     if (select modifieddate inserted)   null     begin         return     end      update dbo.tests      set modifieddate = getutcdate()     inserted     i.testid = tests.testid end 

and finally:

create trigger      [dbo].[modifieddateupdatetrigger] on      [dbo].[tests] update  begin     declare @m datetime2(7) = (select modifieddate inserted)     print @m     if (select modifieddate inserted) <> (select i.modifieddate tests t inner join inserted on t.testid = i.testid)     begin         return     end      update dbo.tests      set modifieddate = getutcdate()     inserted     i.testid = tests.testid end 

none of these work desire test case.

you can't use of these methods pretend inserted ever contain 1 row (what if update more 1 test in single statement?). instead, need perform join-based update, , can use coalesce() determine whether different value supplied column (when hasn't, replace getutcdate()). here approach prefer:

create trigger  [dbo].[modifieddateupdatetrigger] on [dbo].[tests] update  begin   set nocount on;    update t     set modifieddate = coalesce(nullif(i.modifieddate,d.modifieddate), getutcdate())     dbo.tests t     inner join inserted      on t.testid = i.testid     inner join deleted d      on t.testid = d.testid; end go 

the reason complication this. consider simple table:

create table dbo.tests(testid int, modifieddate datetime, x varchar(1)); 

now, 2 rows:

insert dbo.tests(testid) select 1,getdate() union select 2,getdate(); 

we want update modifieddate column in scenario:

update dbo.tests set x = 'y'; 

but in scenario:

update dbo.tests set x = 'y', modifieddate = case   when testid = 1 '19000101' else modifieddate end   testid in (1,2); 

both of these handled adequately multi-row updates trigger, latter might handled incorrectly if assume value supplied in inserted new one. need compare deleted ensure has changed. thing makes hard do? set value explicitly null (well, without disabling trigger). :-) in case replace null passed getutcdate(). not you'd ever want that, didn't want leave unsaid.

---

update

in comments mentioned 1 case want prevent modifieddate getting "bumped" if hard-coded same modifieddate value in subsequent update statements. couldn't find way of differentiating in after trigger (since table has been changed, , @ point it's impossible tell if current update or previous one), did find way differentiate in instead of trigger. if can change instead of trigger, can - additional complication have account other columns might not mentioned explicitly in update statement or want handle differently depending on before/after values. here trigger:

create trigger  [dbo].[modifieddateupdatetrigger] on [dbo].[tests] instead of update  begin   set nocount on;    update src      set src.name = i.name, /* other columns might updated */       src.modifieddate = case          when i.modifieddate <> src.modifieddate case            when update(modifieddate) i.modifieddate else getutcdate() end         when i.modifieddate = src.modifieddate case            when not update(modifieddate) getutcdate() else src.modifieddate end         else getutcdate()        end     dbo.tests src     inner join inserted     on i.testid = src.testid     inner join deleted d     on i.testid = d.testid; end go 

and here demo: http://sqlfiddle.com/#!3/4a00b5/1

i'm not 100% confident else condition required, exhausted time invest in testing today.