March 23, 2005 at 2:24 pm
The guy that makes the decisions about database design in my company has decided that all new tables need to have a field to identify who added a record or made a change the time these actions are taken. I told him that I don't think this is a good idea for every table but I was overruled so ... here we go.
I am just wondering what would be the best way to do this. Should I just use defaults that supply the user_name() and getdate() or should I use triggers? Which is more efficient?
Also, even if I use defaults, I believe this would only be accurate on the initial insert of the record. I think I would still need an UPDATE trigger to handle changes. Does this sound right to you?
Thanks,
hawg
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
March 23, 2005 at 3:05 pm
Defaults are more efficient, but they don't provide a true audit trail as they are easily overriden by supplying values. Triggers are harder to disable/override and so provide a more secure, reliable, and flexible audit functionality.
You are correct that defaults are totally incapable of dealing with auditing UPDATES.
Triggers are the only way to provide real audit trail (apart from some third party tools), but be prepared to spend quite a bit of time writing the things in the first place and then maintaining them afterwards.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
March 24, 2005 at 8:04 am
You should also recognize that a Trigger can be altered, disabled, or dropped by any person with sufficient permissions. Log-reading applications can solve some of this, but Logs can also be truncated with sufficient permissions.
March 24, 2005 at 8:53 am
IMHO, [the guy who makes database design decisions] has a good idea. Shops I have worked in have found this sort of information very useful in tracking down problems, be they programatic or operational.
If you can trust your developers (alas, possibly not!), you can avoid the trigger overhead by having the insert/update code set the field values. One way to do this would be to use stored procedures for all data modifications. These (or the triggers) can be automatically generated, see:
http://www.sqlsavior.com/construct_procs.html
In some cases it is desirable to have a history of all row versions. This gets a little more complicated, but has also been automated:
http://www.sqlsavior.com/datahistorian.html
March 24, 2005 at 11:12 am
We use sps for ALL table inserts and updates (and deletes). We also use NT Auth and store the NT account and domain name in a 'Users' table along with the users name, etc. We also have a user_id column as the PK so the NT Account isn’t the PK, but possibly could be.
We have these 4 columns in many 'work' tables: InsertDt, InsertBy_UserID, LastModifiedDt, LastModifiedBy_UserID.
In both the INSERT and UPDATE sp I have 2 'standard' lines:
Declare @userid smallint
Select @userid = User_Id From Users Where User_NTDomain + '\' + User_Login = sUser_sName()
Ok, so now I have the 'user id' of the person doing the insert or update. In the INSERT sp I set Insertdt to getdate() and then InsertBy_UserID to @User_Id. in the UPDATE sp I set LastModifiedDt to getdate(), LastModifiedBy_UserID to @User_Id.
So now we have who entered it AND who last updated. We don’t track who entered what data, and who updated what data but we can look at a record and see what that last person did. Same goes if a record has never been updated - we no who and what they entered.
For deletes, if tracking them is also important, we have a 'history' table that is almost identical to the 'work table' (i usually don’t have any FKs at all on it). when a user deletes a record (by sps only), in the delete trigger i just place a copy of the 'important' fields of the deleted record (might be all fields) into the history table; here i just have a default on a DeletedBy column for suser_sname() and DeletedDt for getdate(). we seldom have to go digging in these tables so suser_sname() is sufficient, but you could get the user_id from the users table if you wanted to, just like above for inserts or updates.
In our exe we have tabs in certain places where you can see who entered the record and who last updated it.
oh, and it might be obvious, but thought i would add: the InsertBy_UserID, LastModifiedBy_UserID have a FK on the User_ID column on the users table.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply