Audit Trail Table

Hi All,

I want to have an audit trail, which I could track user activity on a table.

1. Write all data from a data table to audit table.
2. Write changed  data  from ? to audit table.

Let' s say an audit table look like :

1. Id   BigInt
2. ColumnName varchar(max)
3. ColumnValue as varchar(max)

How could I do this ?
Please suggest good schema for the audit table.

Thank you.
LVL 1
emi_sastraAsked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
Is there one particular table  you want to monitor / audit ?

You could create a trigger and capture Inserted and deleted. They are special built in tables we can use...

By way of example (and be careful, we tidy up at the end)
create table tbl_my_data_table (id int, customer int, ordernum int, linenum int, product int, qty int, price money)
GO

create table tbl_my_audit_table (id int identity, ActivityDate datetime, ActivitySPID int, Activity char(1), dataid int, customer int, ordernum int, linenum int, product int, qty int, price money)
GO

CREATE Trigger TRG_audit_my_data_table on tbl_my_data_table
AFTER INSERT, UPDATE, DELETE   
AS  

    set nocount on
    declare @Activity char(1)

	if exists (select * from inserted)
      if exists (select * from deleted)
         select @Activity = 'U'
      else
         select @Activity = 'I'
    else
       select @Activity = 'D'

    If @Activity = 'I'
       insert tbl_my_audit_table
	   select getdate(), @@SPID, 'I',*
	   from inserted 

   if @Activity in ('U','D')
       insert tbl_my_audit_table
	   select getdate(), @@SPID, @Activity,*
	   from deleted 

    If @Activity = 'U'
       insert tbl_my_audit_table
	   select getdate(), @@SPID, 'N',*
	   from inserted 

GO



insert tbl_my_data_table values (1,1,1,1,1,1,2.50)
insert tbl_my_data_table values (2,1,1,1,1,2,3.50)
go

update tbl_my_data_table set price = 3.50 where id = 1
go

delete tbl_my_data_table where id = 2
go

select * from tbl_my_audit_table
go

-- now tidy up

drop trigger TRG_audit_my_data_table
drop table tbl_my_audit_table
drop table tbl_my_data_table

Open in new window

Now, should really use user-id rather than SPID (or have both columns) and need to use column names for table being inserted, and columns names being selected and dont use '*'

But hope it makes sense and enough of an example...

P.S. I have created an all in one trigger, but you could create 3 triggers one for each of insert, update and delete.
0
 
Ares KurkluSoftware EngineerCommented:
0
 
emi_sastraAuthor Commented:
Hi Ares,

It is interesting.The description is huge at the link.
Would you please describe how it works ?

Thank you.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
emi_sastraAuthor Commented:
Hi Mark,

What is activity 'N' means /

Thank you.
0
 
Mark WillsTopic AdvisorCommented:
'N' means new value. Wanted to differentiate between the only two states of Inserted and Deleted. Easier to match up 'pairs'.

But if your trigger is one for Insert, and another for Update and another for Deleted, then the Update trigger cn have the Delete (old)+ Insert (new) pairs.

It is entirely up to you - so long as you can easily identify the actual activity. And Update is the only activity that has both deleted and inserted.

One company I worked with insisted on 1 and 2 so when they reported, it was in sequence of delete then insert.

Does that make sense ?
0
 
emi_sastraAuthor Commented:
3      2018-02-17 05:15:32.727      51      U      1      1      1      1      1      1      2.50
4      2018-02-17 05:15:32.727      51      N      1      1      1      1      1      1      3.50

The first row is to record date time updated and also its old value ?
The second row is to record the change ?

Thank you.
0
 
emi_sastraAuthor Commented:
- and dont use '*'
Why ?

- P.S. I have created an all in one trigger, but you could create 3 triggers one for each of insert, update and delete.
What is the advantage and disadvantage of combine and separate it ?

Thank you.
0
 
Mark WillsTopic AdvisorCommented:
Row ID's 3 and 4, yes, 3 is the old value (from deleted) and 4 is the new value (from inserted)

Why not '*' ? Because you have two different tables and any change to either one might break the trigger and your transactions are then compromised. For example, say I wanted to capture more information in my Audit Table, so I change the structure. Until such time I can change the trigger, it will be failing because columns to be inserted dont match columns being selected.

The advantage of having 3 triggers is being able to separately identify discrete transactions (mainly the update). Secondary benefit is not having to check for the type of activity.
0
 
emi_sastraAuthor Commented:
'N' means new value. Wanted to differentiate between the only two states of Inserted and Deleted. Easier to match up 'pairs'.


3      2018-02-17 05:15:32.727      51      U      1      1      1      1      1      1      2.50
4      2018-02-17 05:15:32.727      51      N      1      1      1      1      1      1      3.50

Why dataId both = 1, why id 4 has dataId = 3 ?

Thank you.
0
 
emi_sastraAuthor Commented:
'N' means new value. Wanted to differentiate between the only two states of Inserted and Deleted. Easier to match up 'pairs'.

3      2018-02-17 05:15:32.727      51      U      1      1      1      1      1      1      2.50
4      2018-02-17 05:15:32.727      51      N      1      1      1      1      1      1      3.50

Why dataId both = 1, why id 4 has dataId <> 3 ?

Thank you.
0
 
Mark WillsTopic AdvisorCommented:
3 & 4 are the audit table id's - an identity column in the audit table so it auto increments every insert.

Should be a primary key.... but havent put any indexes on the example tables.
0
 
emi_sastraAuthor Commented:
Hi All,

Thank you very much for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.