Can I have a date+timestamp column in sql server db update automatically on insert or update?

Question

I want to have a column in my tables called ModifiedDate that automatically updates when a row is inserted or updated. Can this be done without writing code in the c# clients that use the db?

Copying a similar column in the AdventureWorks2008R2 sample database, I tried using a datetime2 data type + specifying the column Default Value as "(getdate())" but that doesn't work for me

Is there a way to do this at the database level so I'm not relying on the client app coders to set the field in their c# app ?

Answer 1

Look up the SQL documentation (copy paste is not going to make you smarter) - the keyword you look for is TRIGGER.A trigger can react to inserts / updates / deeltes and update the data inserted / updated.

Answer 2

How about using default values? During insert , if you are not providing any value, the column will be updated with the provided default value.

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)