In each one of our tables we have created a last_change_date column (type datetime). We would like this column to be updated automatically whenever a row is touched. For insert it is easy we can bind the column to a default which can use getdate() function.. But if the row is updated is there any way to update the column without having to specify above column in the column list for update?
Another option if you dont mind explicitly mentioning the column in every insert/update statement is getdate() with also a default constraint of getdate() maybe.
But it has loopholes and not foolproof. If someone goes and manually updates a column using ISQL for eg., you have no choice of tracking it. Thats the reason for pdreyer's answer which covers all audit points.