Best way to stamp records with who created / modified and when?
-
I'm designing a database in which I'd like to record who created and last modified each record, and timestamp them. This data will mostly be used for support purposes - I have found it handy in the past to be able to say to a user that they are guilty of chaning a record at a particular time. Keeps them honest. Preferably this would be as transparent as possible to the developers of systems that will use this database.
My idea is to have 4 extra fields per record: CreatedBy, CreatedDate, ModifiedBy, ModifiedDate. I can use the functions suser_sname() and getdate() to return the data. So far so easy, but what's the best transparent way to populate these fields?
The row creation stamping is pretty simple: I can use the functions as defaults for the fields, so any Insert that doesn't set the fields gets stamped.
For row modification stamping I have more of a quandary. I could use triggers but instinct says (in a Bill & Ted voice) "NO WAY". I could force the developers to set these fields in their stored procs. I am in full charge here (oh dear...) so could enforce this.
Is there any better way? Is there some system table that's recording this anyway, or something in the database logs? I want something pragmatic. Reading the data won't happen all that often, and I really don't want clunky update queries.
Thanks,
Bill.
-
Bill - not sure why you think the update queries would be "clunky"...I have a VB 6.0 front-end which uses the windows api to get the "userName" and "Now()" for the "ModifiedDate" that're used for all the "updates"....
**ASCII stupid question, get a stupid ANSI !!!** -
It's just really that the developers won't have the discipline and might push back a bit. Whenever I've done this as a developer it wasn't too bad, but then I'm a bit of a stickler.
If I can automate this then everyone wins.
Bill.
-
Since you're a stickler AND "in charge" make the most of your powers and take the stick to any developer who slacks off...that'll discipline 'em soon enough...
**ASCII stupid question, get a stupid ANSI !!!** -
Don't fight 'em... don't tell 'em... put triggers on the tables for these. Most developers don't even know how to spell trigger never mind go looking for them.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
For inserts, you can use defaults and for updates, see the article "Triggers for Auditing" at
http://www.sqlservercentral.com/columnists/tsilva/triggersforauditing.asp
SQL = Scarcely Qualifies as a Language
-
Triggers or columns hmmm ... there is no 'right or 'NO WAY' (wrong answer) ... it all depends ... if the system is OLTP, with low volume (<100 transactions minute), then triggers would do in most cases. If volume is greater than that then I'd say columns. However if you have 'distributed' or 'long running ' transactions then 'triggers' is not the way to go. In either event, it sounds like you need a 'site standard' or a 'best practice'. Just remember that when speaking of 'standards' and 'practices' that they are an evolving thing, they change and that there are always exceptions.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Defaults don't work on rows that have already been inserted. Leaving it up to Developers to remember to include it in code is OK provided the code has to go through a pre-production code review or they'll forget.
Triggers are the only guaranteed way to go to update LMB columns even for large volumes if written correctly (set-based instead of as in the example given in the link previously posted). They will easily handle in excess of 250,000 transactions per minute if done correctly and with the correct locking applied. Triggers are a great mechanism for LMB columns because you set 'em and forget 'em. So long as you DON'T use RBAR (Row By Agonizing Row) methods, there should be no problem with performance. If written correctly, the same LMB trigger can be used for both INSERTs and UPDATEs.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
First of all do not change the schema of traced tables.
Create separate table having columns:
ObjectId, PKValue, UserId, OperationId, TimeRecorded.
Than set up triggers on traced tables to insert references to their records mentioned in tables INSERTED and DELETED.
Of course you'll need tables Object, Operation, User. Don't use value from Object_ID() for your records because if table was redesigned from EM it was dropped and recreated wit new ID, so your previous records about this table will become meaningless.
_____________
Code for TallyGenerator -
And in separate table you can trace not only inserted or updated rows, but deleted ones as well.
_____________
Code for TallyGenerator -
Thanks to everyone - what a great response.
None of this is for significant scale OLTP, so the reason I am shy of triggers is simply that so many people forget they are there and end up doing things that they didn't want. The level of developer ignorance here isn't too bad, but old lags like me can still catch them out, making me nervous about being complex unless there's a good reason. Triggers caught me out very badly in the past in a system with 100+ tables, 1000+ SPs, and 100+ triggers which contained application logic... At least I was only trying to support someone else's mess so couldn't be blamed.
At first glance, I like Segiy's method the best - this will give me a fuller audit than I originally considered. I'll work on getting that running.
Once again, thanks to all who took the time to help.
Bill.
-
Hopefully the last question:
I've started building my triggers. The first is:
CREATE TRIGGER ChinaSecurityTypesInsert ON [dbo].[ChinaSecurityTypes]
FOR INSERT
AS
INSERT INTO dbo.Operations (TableName, PKValue, OperationType, Detail)
SELECT 'ChinaSecurityTypes', ChinaSecurityTypeId, 'I', NULL FROM INSERTED
This is marvellous, but it seems clunky to have to have a literal for the table name. Is there a way of referencing it?
Bill.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply