?
Solved

Sql Query help

Posted on 2008-06-09
3
Medium Priority
?
214 Views
Last Modified: 2011-09-20
I have an audit table T with around 11 columns. Say FK, C1,C2,C3.... C10. Here FK is the guid of the corresponding row in the original table. Whenever any data changes in the original table, I write the after image of the data to this audit table.

What I would like to do is write a function/stored proc to return a table. The func or SP takes the "FK" as input parameter. The result set should be like:

DateTime of Change          |           Change Summary
6/2/2008 10:23:34.033      |           C2 (Data --> Changed), C5 (Original --> Changed)
6/5/2008 18:09:00.033      |           C8 (Data --> Changed)
.................
.................
...................

Basically, for each row, I want to summarize the change in the above format, rather than just printing out the audit rows.

0
Comment
Question by:dencom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 2

Expert Comment

by:vasureddym
ID: 21749965
what is the difference between Data --> Changed and Original --> Changed
0
 
LVL 2

Accepted Solution

by:
vasureddym earned 2000 total points
ID: 21750039
I have done some thing very similar long back which calculates the time difference between each record. but this procedure will be very heavy. if no other expert suggests a better solution, please use this.

You have to use cursors and two variables for each column you wanted to track (@PreviousC1Value, @CurrentC1Value)

1) Declare and open the cursor (with Select * from yourAuditTable where FK = <value>)
2) Declare the required variables
3) Fetch the first row, store the column in @PreviousC1Value (like wise store all the other columns that you are monitoring into the corresponding values)
4) Start the cursor loop
     4a)Fetch the next row, store the column in @CurrentC1Value
     4b)Compare the @PreviousC1Value with @CurrentC1Value and if any difference store this message in to a string variable.  (like wise for all other columns, append to the same string variable with comma separated)
     4c) insert a record into Results table (the time stamp, and the string variable that is build)
     4d) move the @CurrentC1Value into @PreviousC1Value (like wise for all other columns)
5) End the cursor loop
6) close and deallocate cursor
7) select * from ResultsTable

There are many other ways to do this instead of cursors, but cursors is a straight forward method

-
Srinivas
0
 

Author Comment

by:dencom
ID: 21750879
Yeah, I knew the default option is the cursor. I know you looked at set based soln, but I am going to keep the Q open to see if anybody has any other ideas. Thanks.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question