1

I have a table like this:

id  name    modified
11  John    2016-07-12 15:49:45
22  Abraham 2016-07-12 15:52:03

I need to update the 'modified' column which tracks the last modified date for a row. I have done this using a trigger, but have read that triggers eat up performance. Is there a way to do this using constraints?

curiousboy
  • 135
  • 2
  • 13

1 Answers1

0

It's possible to use DEFAULT constraint and DEFAULT keyword in UPDATE clause. See the following example:

CREATE TABLE UpdateTest
(
  ID int IDENTITY,
  Name varchar(10),
  Modified datetime2(2) CONSTRAINT DF_Modified DEFAULT (SYSDATETIME())
)

--ID from IDENTITY, Modified from DEFAULT implicitly
INSERT UpdateTest(Name) VALUES('Test')

--Modified from DEFAULT explicitly
UPDATE UpdateTest SET Name='Test2', Modified=DEFAULT
Paweł Dyl
  • 8,888
  • 1
  • 11
  • 27
  • Dyl: Thank you. during updates, we need to pass 'DEFAULT' parameter for modified column explicitly, is there a way to get the modified date without passing any value to modified column? – curiousboy Jul 14 '16 at 04:36
  • If i had to pass a parameter for modified column, i could set Modified column value as getdate() for every updation. ex; update updateTest SET Name='Test2', Modified=getdate() where ID='some_value'. i can pass getdate() everytime in my java code also. sorry i did not mention that criteria in my question, can you please re check for alternative. – curiousboy Jul 14 '16 at 04:47
  • As far as I know it's not possible to omit DEFAULT keyword or explicit GETDATE() invocation. SQL Server must somehow distinguish between updating column and leaving value in updated row unchanged. Logically, some kind of distinguisher must exist. – Paweł Dyl Jul 14 '16 at 08:00