| Author |
Topic |
|
Burntchips
Starting Member
4 Posts |
 Posted - 2010-05-10 : 16:13:38
|
| I'm new to SQL Server and I'm trying to add 2 simple audit fields to a table.I have a user table and I have added two fields 'createDate', 'lastUpdated'. I want these two fields to be managed by triggers. I have thought of a couple of ways to do this but have hit some small snags.I Created a AFTER trigger for update and insert on this table on insert udpate the createDate field on update update the lastUpdated field. This seemed like a good solution but has a small problem, when the insert updates the createDate field the update trigger fires and also updates the lastUpdated field. To get around this I thought of two possibilities: disable the trigger before the insert trigger updates then re enable (kind of ugly) or in the update trigger ignore any updates to the createDate field (works well but if someone were to update the createDate field after the row was created the lastUpdated would not be updated)I guess I could also create an INSTEAD OF trigger but this seems messy and could hit the same problems.what is the common way of doing this type of auditing? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-05-10 : 17:30:56
|
quote:
Originally posted by tkizer
Why can't you use a default of GETDATE() for createDate and then manage lastUpdated via the UPDATE command and GETDATE()?
It's an audit field, so any user who has update access to the table should not be able to make changes to that field.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-11 : 02:50:15
|
We don't change the UpdateDate using a Trigger (although we used to  ) we do it in the "SAVE" SProcs. Reason being that sometimes we transfer data from one database to another and we do NOT want the UpdateDate column changed [to reflect the insertion date into the new database], we want to leave the UpdateDate column value representing the original, human, change.Your needs may be different though. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-05-11 : 03:53:42
|
| A default value of GETDATE() for createdDate is my best bet also, along with an update trigger. The most efficient would be to set the updated-column through the procedure that does the actual update but there will always be some moron developer that forgets this :) I have created a script for creating audit-tracking to tables, it does quite a lot more than what you require here but maybe you'll find something useful. It will basically add 5 audit columns to a table and keep track of all versions of each single row in a separate audit-table that is a duplicate of the original.-> http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=141844- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-05-11 : 05:26:01
|
quote:
Originally posted by Kristen
We don't change the UpdateDate using a Trigger (although we used to ) we do it in the "SAVE" SProcs. Reason being that sometimes we transfer data from one database to another and we do NOT want the UpdateDate column changed [to reflect the insertion date into the new database], we want to leave the UpdateDate column value representing the original, human, change.Your needs may be different though.
I've seen update triggers that account for this. The import process always ran as a specific user context, so the trigger checked User_Name() and only ran it the user name didn't match.------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-11 : 06:12:43
|
| Nice variation on a theme. I'm guilty of forgetting the UpdateDate column in ad-hoc UPDATE statements :( (only on very rare occasions of course ... and then our Audit Table trigger saves me from myself!) |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-05-11 : 11:36:29
|
| If you don't have good access layer (AKA stored procedures) then auditing with triggers is the least of your worries. Added on triggers to account for a flawed design is not the way to go. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-05-11 : 16:32:34
|
| Respectfully disagree here.This type of auditing should absolutely be handled by a trigger, not procedure logic.It is a completely data-centric business rule, which should be applied as closely to the data as possible. In this case, that means a trigger for LastUpdated, and a default for CreateDate.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2010-05-12 : 02:45:33
|
| I have to disagree with Lamprey as well...sorry man :) There is no way I'll be able to force all database activity to go through stored procedures. Think about 3rd party software for example, you don't have any control of the application whatsoever but you could still enable auditing. And all that ad-hoc stuff you do when someone messes up...no way I'm gonna keep all those audit-columns in mind every single time.That being said; triggers are the devil!! For more or less everything else that auditing... :)- LumbagoIf the facts don't fit the theory, change the facts. Albert Einstein |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-05-12 : 09:24:32
|
| Triggers don't kill databases.Bad SQL programmers kill databases.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
|
Audit Fields -- Best Practice