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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.