Update last modified date column

Question

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?

Answer 1

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