September 13, 2013 at 11:25 am
Hi All
I have a column in my table named last_modified_date with a default value of Getdate().
I would like the datetime of this column to refresh when I update any of the columns in the table.
I see that when I update the description column to fix a typo, for example, that the last_modified_date column remains the same.
I am not a big fan of triggers and don't want to circle back and modify my code to update and set the last_modified_date = DEFAULT each time I update records for my various tables I plan to include this column on.
Time to ask the audience.... What is the best way to accomplish this?
Thank You
September 13, 2013 at 1:43 pm
A trigger is the best way to handle that.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 13, 2013 at 1:46 pm
If you handled all of your updates via stored proc this would be even easier. 😛 Just add another column to the update statement. If however, as it sounds that you do pass through sql from your apps then as Scott suggested, a trigger is the easiest way to accomplish this.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 13, 2013 at 1:56 pm
Sean Lange (9/13/2013)
If you handled all of your updates via stored proc this would be even easier. 😛 Just add another column to the update statement. If however, as it sounds that you do pass through sql from your apps then as Scott suggested, a trigger is the easiest way to accomplish this.
In theory. In practice, a stored proc may not always be used, even in a production environment. Therefore, the best chance to catch the most UPDATEs possible is to use a trigger.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 13, 2013 at 2:35 pm
ScottPletcher (9/13/2013)
Sean Lange (9/13/2013)
If you handled all of your updates via stored proc this would be even easier. 😛 Just add another column to the update statement. If however, as it sounds that you do pass through sql from your apps then as Scott suggested, a trigger is the easiest way to accomplish this.In theory. In practice, a stored proc may not always be used, even in a production environment. Therefore, the best chance to catch the most UPDATEs possible is to use a trigger.
True. A trigger is more likely to catch more updates than a stored proc. There is no 100% foolproof method here of course.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 13, 2013 at 3:04 pm
Thanks for the replies.
I was hoping more that there was a property in my default value statement that I was missing somewhere. I'm familiar with the methods you guys suggest.
And for the record.... updating via a stored procedure is not the issue here. 😉 Each table gets updated from only one point in the SSIS code. The issue is that I have multiple tables I was going to add these "audit columns" to as an afterthought. That means there is no difference between altering multiple places in my SSIS or altering multiple procs if I go the route of adding an extra column to my update statements.
Agreed that trigger is a good choice for SQL professionals. I'm concerned about varying levels of SQL proficiency amongst teammates. It's been my experience that junior guys tend to miss the triggers in debugging situations so as a "realistic" best practice, I like to keep things more visible. Although, admittedly, an audit column is low risk.
Again, thanks for the input. I'll mull it over to decide the best route.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply