Audit table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Audit table

Author

Topic

SteevR
Starting Member

18 Posts

Posted - 2009-04-21 : 14:48:52

Hi,
This is probably pretty simple, so I aplogize first off for my ineptitude.
I have a trigger based audit table that I want to display in an ASP.Net grid. The problem is some of the collected data is from other tables. For example, empID is an INT ID stored in the audited table, but is actually from tblEmployees.

tblAudit
fieldName oldValue newValue
empID 133 140
taskID 5 7

tblEmployees
empID empName
133 Biff Magoo
140 Joe Boxer

So I want my ouput grid to look like this:

fieldName oldValue newValue
empID Biff Magoo Joe Boxer
taskID someTask someOtherTask

I created a table that shows the relationship, but I dont know for the love of everything pete how to get the proper output or if this is even the best way to do it.

tblRelationShip
PKFieldName fieldValue tableName
empID empName tblEmployees
taskID taskName tblTasks

Any poke in the right direction will be helpful.

TIA,
Steve

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-04-21 : 15:07:50

[code]
select A.fieldname, coalesce(B.empname,C.Taskname) As oldvalue
from tblAudit A
left join tblEmployees B on B.empid = A.oldvalue and A.Fieldname = 'empid'
left join tbltasks C on c.taskid = A.oldvalue and A.Fieldname = 'taskid'
[/code]

Something like this?

------------------------

An infinite universe is the ultimate cartesian product.

SteevR
Starting Member

18 Posts

Posted - 2009-04-21 : 15:22:54

Hi cat_jesus,

Thanks, I tried this and couldn't get it to work. Also, all the audited fields are not IDs from another table; there are text fields as well that have no relationship. That is why I created the relationship table. If the fieldnames are in that table, then it gets the value from its specified table.

I hope this makes sense...

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-04-21 : 15:33:13

How many tables total? If it's under a dozen you can use the approach I outlined above. I didn't flesh it completely out, I was just giving you a nudge.

With the left joins you'll have nulls in all the old value and new value columns save for the one that's valid for that audit row.

With coalesce you can use many possible columns and the function will use the first non null value.

for example: coalesce(A.oldvalue,B.oldvalue,C.oldvalue,D.oldvalue,E.oldvalue) as oldvalue

However, if you have a huge number of tables, you'll have to use a different approach. How many records are in tblrelationship?

_________________________________________________________________

An infinite universe is the ultimate cartesian product.

SteevR
Starting Member

18 Posts

Posted - 2009-04-21 : 15:58:33

Thanks cat_jesus,

There are only about 5 possible fields in the audit table that can be linked.

I tried executing your statement with just one linked field and fails with error: cannot convert 'Apr 3 2009' to data type int.

SELECT A.FieldName, COALESCE (A.NewValue, B.FName) AS newvalue
FROM Audit AS A LEFT OUTER JOIN
tblEmployeePersonal AS B ON B.empID = A.NewValue AND A.FieldName = 'empID'

There are many other fields that oldValue and newValue can be besides the linked IDs; dates and text included.

Thanks,
Steve

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-04-21 : 17:14:11

Yeah you'd have to cast or convert them all to a varchar, which you'd have to do anyway for a report.

No biggie

COALESCE (convert(varchar(25),A.NewValue), convert(varchar(25),B.FName))

or something like that.

An infinite universe is the ultimate cartesian product.

SteevR
Starting Member

18 Posts

Posted - 2009-04-22 : 08:38:37

Hi cat_jesus,

I think the problem lies in the join as both newValue and oldValue fields are already varchar. Here is an example of the audit table data:

fieldName oldValue newValue
empID 135 140
taskID 5 7
entryDate NULL Apr 3 2009
priorityID NULL 3
estimatedTime 5 Hours 6 Days

Thanks for your patience,
Steve

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 2009-04-22 : 10:35:38

quote:
Originally posted by SteevR

Hi cat_jesus,

I think the problem lies in the join as both newValue and oldValue fields are already varchar. Here is an example of the audit table data:

fieldName oldValue newValue
empID 135 140
taskID 5 7
entryDate NULL Apr 3 2009
priorityID NULL 3
estimatedTime 5 Hours 6 Days

Thanks for your patience,
Steve

Well you would have to cast or convert in the join too. For example :

left join tblEmployees B on convert(varchar(25),B.empid) = A.oldvalue and A.Fieldname = 'empid'

An infinite universe is the ultimate cartesian product.

SteevR
Starting Member

18 Posts

Posted - 2009-04-22 : 13:46:25

Hi cat_jesus,

Thanks it works great. The SQL looks quite convoluted when finished but it works great.

Great nudge... although it turned out you had to walk me down the whole path... eep.

Steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources