Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VIEW CHANGES IN A TABLE

Posted on 2005-05-11
10
Medium Priority
?
159 Views
Last Modified: 2012-05-07
I produce a table for reservations in different courses.

There are fields in the table that I pull from servers that can change from day to day.

Is there a way to view what fields have changed when I run the query.

I.E.

One day a person can be enrolled DL and the next it could change to a resident course.
Would there be a way to turn this specific fields font to red to show that there was a change from the previous table?
0
Comment
Question by:IZIKILL2
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13981131
You would need to have fields in the tables such as dateinserted, datemodified, insertedby and modified by, along with the appropriate application logic to update those fields
0
 

Author Comment

by:IZIKILL2
ID: 13981425
Im really looking for a way to compare the old and new table and be able to track the changes or new entries without adding a date mod function.
0
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 2000 total points
ID: 13981505
www.red-gate.com

SQL Data Compare will compare identically structured tables and highlight new, missing, different and/or identical rows for you, as well as write the scritps to synch them

Better than writing scripts to compare every field on your own...

14 day trial, $199 to buy
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 11

Expert Comment

by:lluthien
ID: 13981869
>One day a person can be enrolled DL and the next it could change to a resident course.
>Would there be a way to turn this specific fields font to red to show that there was a change from the previous table?

this would depend on the frontend you are using, is it a website?
that is just about the colouring.

if you want to detect the changes, you need to be able to get to the old values of the data.
so if you want to compare the data tomorrow, you need to backup today's data.
so copy today's table to a table "yesterday" and tomorrow, you can compare it with the newly entered data.

this works for just one day tho.
0
 

Author Comment

by:IZIKILL2
ID: 13982533
I have access to the old tables.  How would I go about comparing them though. The table has 40,000 records.  So maybe a field that says there was a change from the old field would be useful.

Really there are only about 5 or so fields I would apply this to.

A Person was not scheduled yesterday, today he is.  I would like to be able to see that.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13982676
The two tables have to co-exist in order to compare them.  There has to be a way to identify them.  About the only way you are going to be able to see what changed is to have an archive table of yesterday's data to compare against today's data.  re-populate every night if you only need one day of changes...

Alternatively, write triggers to insert the entire row into an archive table on Delete and Update
0
 

Author Comment

by:IZIKILL2
ID: 13983116
I can archive it every night if need be and have both the old and new table.
0
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13983443
If that is all you need, to be able to compare one table to the previous days, then that may be your best bet.  And 40K records won't even take up that much space...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13983491
If you do not need to know what data changed, but simply rather which column(s) changed, consider adding an addtional column in the table that would tell you which column(s) were modified.
0
 
LVL 11

Expert Comment

by:lluthien
ID: 13984279
if you post some of the db structure,
we might be able to help you better
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

580 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