Automatically update Last Modified Date in ASP.Net MVC app

Question

General Info:

1. ASP.NET MVC app

2. using ADO.NET Entity Framework

3. SQL Server 2005

I have a few tables that more or less have a hierarchical structure of mostly one-to-many relationships. Most if not all of them have a Last Modified column and my question is simply how best to go about making sure that this column is updated properly throughout the hierarchy. I would prefer to have it done at the database level automatically, I'm just not sure how. A colleague mentioned triggers while at the same time mentioning that they might not be the best way to go. I don't know whether to do this within the class I'm using to manage the model or at the database level. I'd prefer not to have to keep updating each reference individually as that gets tedious and I'd have to make a bunch of separate functions for each level.

My questions then are:

1. Is there some way to do this at the database level with a stored procedure I could call?

2. If not, what would you suggest I do on the application side of things to best handle this programmatically?

If any more info would be helpful, I'll be happy to provide it, I had a difficult time trying to figure out how to even ask this question.

Answer 1

Another person had the same/similar question here. Their opinion use a trigger. Here's the full response: http://stackoverflow.com/questions/516517/sql-server-entity-framework-createdat-updatedat-columns

Answer 2

It depends on where you want your penalty to be. You can have it one of two ways.

• Use a trigger to propagate changes up your hierarchy. This might be best when you write once and read a lot. eg: Update an address and the last modified gets set on the parent records as well as the address itself.

• Just update one last modified and then query for everything when you need to see when something has been modified. This might be best if you write a lot, but read very little. eg: Only update the last modified on an address. When you query for changed records, query for records that have a last modified or records with addresses that have a last modified.

It's hard to say without knowing your situation. If you need to update all related last modified dates, then I would go the trigger route just to keep things simple.