Need a datetime column in SQL Server that automatically updates when the record is modified

by

SQL Server doesn’t have a way to define a default value for UPDATE.

So you need to add a column with default value for inserting:

ADD modstamp DATETIME2 NULL DEFAULT GETDATE()

And add a trigger on that table:

CREATE TRIGGER tgr_modstamp
ON **TABLENAME**
AFTER UPDATE AS
  UPDATE **TABLENAME**
  SET ModStamp = GETDATE()
  WHERE **ID** IN (SELECT DISTINCT **ID** FROM Inserted)

And yes, you need to specify a identity column for each trigger.

CAUTION: take care when inserting columns on tables where you don’t know the code of the application. If your app have INSERT VALUES command without column definition, it will raise errors even with default value on new columns.

More Related Contents:

  1. Check if table exists in SQL Server
  2. How do I create a unique constraint that also allows nulls?
  3. How do I get list of all tables in a database using TSQL?
  4. How to convert SQL Server’s timestamp column to datetime format
  5. Get first day of week in SQL Server
  6. What is the significance of 1/1/1753 in SQL Server?
  7. Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
  8. Escape a string in SQL Server so that it is safe to use in LIKE expression
  9. Insert Update trigger how to determine if insert or update
  10. SQL time difference between two dates result in hh:mm:ss
  11. What is the best way to truncate a date in SQL Server?
  12. Dynamic SQL (passing table name as parameter)
  13. SQL Server: Difference between PARTITION BY and GROUP BY
  14. How do you truncate all tables in a database using TSQL?
  15. TSQL Email Validation (without regex)
  16. Dynamic SQL results into temp table in SQL Stored procedure
  17. SQL Server Output Clause into a scalar variable
  18. Sequence as default value for a column
  19. Aggregate bitwise-OR in a subquery
  20. TABLOCK vs TABLOCKX
  21. How to convert number of minutes to hh:mm format in TSQL?
  22. How do I select last 5 rows in a table without sorting?
  23. T-SQL incrementing counter based on change in a column value
  24. SELECT INTO a table variable in T-SQL
  25. T-SQL query to show table definition?
  26. How can I insert binary file data into a binary SQL field using a simple insert statement?
  27. Cannot find either column “dbo” or the user-defined function or aggregate “dbo.Splitfn”, or the name is ambiguous
  28. Include missing months in Group By query
  29. Is there an “IF EXISTS” test for a linked server?
  30. SQL Server stored procedure return code oddity

Leave a Comment