SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Audit table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SteevR
Starting Member

18 Posts

Posted - 04/21/2009 :  14:48:52  Show Profile  Reply with Quote
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 - 04/21/2009 :  15:07:50  Show Profile  Reply with Quote

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'


Something like this?



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

An infinite universe is the ultimate cartesian product.
Go to Top of Page

SteevR
Starting Member

18 Posts

Posted - 04/21/2009 :  15:22:54  Show Profile  Reply with Quote
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...
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 04/21/2009 :  15:33:13  Show Profile  Reply with Quote
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.
Go to Top of Page

SteevR
Starting Member

18 Posts

Posted - 04/21/2009 :  15:58:33  Show Profile  Reply with Quote
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
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 04/21/2009 :  17:14:11  Show Profile  Reply with Quote
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.
Go to Top of Page

SteevR
Starting Member

18 Posts

Posted - 04/22/2009 :  08:38:37  Show Profile  Reply with Quote
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
Go to Top of Page

cat_jesus
Aged Yak Warrior

547 Posts

Posted - 04/22/2009 :  10:35:38  Show Profile  Reply with Quote
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.

Edited by - cat_jesus on 04/22/2009 10:36:14
Go to Top of Page

SteevR
Starting Member

18 Posts

Posted - 04/22/2009 :  13:46:25  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000