SQL Server Performance Forum – Threads Archive

Creating Audit Columns

If I want to detect the date a record was added to the database, do I have to create a trigger? For instance, if I have a login table and I want to know the date the new user id was created, will I manually have to code for that or is there another way in the table definition to do this? Thanks.

There’s several ways to do this. –You could just create a create_date column and populate it in the stored procedure that creates users (preferrable).
–You could create column with DEFAULT(GETDATE()) and let it populate itself on INSERT.
–You could use a trigger if you were just feeling frisky.
–You could create a dilithium-ion trasflux capacitator and populate it. This would also be kewl, but it’s probably a little overkill. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> You could create a dilithium-ion trasflux capacitator <hr height="1" noshade id="quote"></blockquote id="quote"> wtf does this mean? [<img src=’/community/emoticons/emotion-2.gif’ alt=’

‘ />] Anyway, if you only care about INSERT and not about UPDATE of the data, I would go for two additional columns. One for the date with a DEFAULT of GETDATE() or in case, you don’t want to see the time DATEADD(d,0,DATEDIFF(d,0,GETDATE())) and another for the user who added this row with a DEFAULT of SUSER_SNAME(). I also think this is preferrable to a stored procedure approach, because it enforces the integrity where it belongs. Imagine what happens, when someone enters data without using the procedure? Doh…[<img src=’/community/emoticons/emotion-3.gif’ alt=’:O’ />] – Frank Kalis SQL Server MVP http://www.insidesql.de</a>

I prefer method one and you allow ZERO direct SELECT, UPDATE, INSERT, or DELETE rights on the table. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] The trasflux capacitator takes care of the people not using stored procs anyway. MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

Lucky guy if you don’t have a more or less dynamic environment and no "power users" where you work. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] <blockquote id="quote">quote:<hr height="1" noshade id="quote"> The trasflux capacitator takes care of the people not using stored procs anyway <hr height="1" noshade id="quote"></blockquote id="quote"> I suspected this to be a great thing anyway. Sounds to me like something taken from "Back to the future". How is the phrase: "Mc Fly, anyone home?" ? – Frank Kalis SQL Server MVP http://www.insidesql.de</a>

…btw, it doesn’t hurt to have those DEFAULTs, even if you work with a sproc. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

Thanks for all the help. I am sure the answer lies in between.

We’re on the same page here Frank. Just having fun. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] MeanOldDBA [email protected] When life gives you a lemon, fire the DBA.

]]>