best way to make an audit record

Namasi is right.. create trigger for that..

good tutorial
http://www.nigelrivett.net/Triggers_1_A_beginners_guide.html

http://www.informit.com/isapi/guide~sqlserver/seq_id~57/guide/content.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp (MSDN syntax)

HTH
Dishan

Will be fine assuming you're not using an application role to log into the database.  If user authentication is provided by a front end application you may have to parse a username to sql server.  I do so by including the username in the connection string under the workstation property.  Something like this 'workstation ID=MyUserName'

Then you can use the Host_Name() function to return the username from within the trigger.

Agree with Eugenez, we have an application we use triggers on to log the changes, but it's by far the slowest part of the application.  If you have lots of columns, be prepared for lots of overhead....

To reduce the overhead as much as possible, make sure the trigger is as efficient as possible.  Sometimes poor trigger coding is the reason for bad performance.

all good feed back. I like the various options here. I'll look into the log examination, that seems like an efficient method to do it. DO you have to stop your SQL server to retrieve the logs?

www.lumigent.com, looks like a good tool, but expensive, anyone else know of a cheaper log file analyzer for creating audit trails?

Joe

They're all pretty pricey because not too many people have them--take a look at NetIQ (don't remember the name of their tool)  http://www.netiq.com

Brett

try the trigger - from all that I have it lightest:



CREATE trigger dbo.AUDIT_tablename_updates ON dbo.tablename
for update
as
set nocount on
insert into audit_tablename
select 'before',0,suser_sname(),GetDate(),* from deleted
insert into audit_tablename
select 'after',SCOPE_IDENTITY(),suser_sname(),GetDate(),* from inserted

go

ok, sorry about the delay, I've begun building the audit functionality into my DB. I've got namasi_navaretnam's suggested trigger working.

But I've got an idea. Instead of creating an audit table that has the same data structure as my real table, and having to update both tables all the time, I'm going to try to create two generic audit tables with this data structure:
-----------------------------
AFTaudit1 TABLE
a_user: user who makes update.
a_datetime: date and time of update
auditID: primary key which serves as foriegn key to AFTaudit2 table.

AFTaudit2 TABLE
auditID: foriegn key to AFTaudit1
field: name of field that has changed.
oldValue: value before update or delete or insert
-----------------------------

ok, this is where I need some help. Here is my trigger (I have left the delete and insert branches out for simplicity). See the comment below in my trigger. What is the SQL syntax to accomplish this?

-----------
CREATE TRIGGER trigAudit ON dbo.CktMain
FOR INSERT, UPDATE, DELETE
AS
Begin
   SET NOCOUNT ON
   If exists(select 1 from inserted)
   Begin
      If Exists(select 1 from deleted)
      Begin
         -- update trigger
         INSERT AFTaudit1
         SELECT  USER_NAME(), GetDate(), 'UPDATE', APP_NAME() -- insert headers first. Whenever you add a column to actual table add a column to audit table as well.
         FROM inserted  
      
       --I need to loop through each of the fields that has changed and write to the AFTaudit2 table the old value and the fieldname, using the auditID that was just assigned to AFTaudit1
      
      End
   End
End
-----------