How to find changes in a table

Question

How to find changes in a table

adams.squared

SSCrazy

Points: 2675
More actions
March 4, 2010 at 10:43 am

#145684

I have a table for called ShipTo, which contains all of the ship to addresses for our customers. I cannot modify this table without violating support for the software. I need to know when a CSR rep adds or changes one of these address in the last 24 hours. There isn't a change or add date field in the table.
How do I go about detecting when someone has made a change in the table?
Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ray Dai SSCommitted Points: 1548 More actions · Answer 1

Can you add a trigger or does that consitute "changing the table"?

What about the part where insert/update happens, can you change that and add an OUTPUT clause to them to insert the results into an audit table?

http://msdn.microsoft.com/en-us/library/ms177564(SQL.90).aspx

adams.squared SSCrazy Points: 2675 More actions · Answer 2

ray-SQL (3/4/2010)
Can you add a trigger or does that consitute "changing the table"?
What about the part where insert/update happens, can you change that and add an OUTPUT clause to them to insert the results into an audit table?
http://msdn.microsoft.com/en-us/library/ms177564(SQL.90).aspx

I thought about that and the answer I got back was that if anything goes wrong, it will be billable support.

what about comparing tables from a snapshot?

Ron McCullough SSC Guru Points: 63877 More actions · Answer 3

I need to know when a CSR rep adds or changes one of these address in the last 24 hours.

How does the CSR add/change an address i.e., through a vendor supplied application which in turn uses a stored procedure or is this unknown to yourself?

If a stored procedure is used can it be modified without destroying support for the product?

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 4

You could compare to snapshots using SQL Data Compare from Redgate.

You could also do a compare in a query like this:

select * from ShipAddr a

inner join ShipAddrSnapshot b

on a.ShipAddrID = b.ShipAddrID

where a.Col1 <> b.Col1

or a.Col2 <> b.Col2 ...

Converting oxygen into carbon dioxide, since 1955.

adams.squared SSCrazy Points: 2675 More actions · Answer 5

bitbucket-25253 (3/4/2010)
I need to know when a CSR rep adds or changes one of these address in the last 24 hours.
How does the CSR add/change an address i.e., through a vendor supplied application which in turn uses a stored procedure or is this unknown to yourself?
If a stored procedure is used can it be modified without destroying support for the product?

The CSR uses a front end ERP. I do not see any obvious stored procedures that would lead me to customer update or creation, but even if I did, I cannot modify it without ending support. What I need is a solution that does not modify the application, either on the front end or the back end. I would have to come up with something else.

What I am looking at doing is setting up some kind of backup/restore of the tables I need and doing some kind of EXCEPT or INTERCEPT to see what has changed with today's vs yesterday's data. Anyone familiar with that? We are running Enterprise. Oh yeah, Replication violates our support as well. Boy I love our ERP.

adams.squared SSCrazy Points: 2675 More actions · Answer 6

To be honest, I was looking to see if there was some kind of flag in the system that would identify that they row had been changed. Doesn't the system mark the row for backups?

Ron McCullough SSC Guru Points: 63877 More actions · Answer 7

When a DB is backed up ... tables are backed up in their entirety. Not just changed/added rows.

Would examining the logfile violate the ERP's support?

Can you run a Trace again without violating support?

And last but not least does your organization have a good attorney? (Said in jest of course, in an attempt to decrease your frustration or should I say take 2 aspirn and continue hunting for a solution.)

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

adams.squared SSCrazy Points: 2675 More actions · Answer 8

bitbucket-25253 (3/4/2010)
When a DB is backed up ... tables are backed up in their entirety. Not just changed/added rows.

When you do a differential backup, it does a backup of what has changed. There is some kind of flag that tells SQL that it needs to back up this particular data, not the whole table. Once it is backed up, the flag is cleared. If I could capture the data that has changed, before the differential backup, then I will be able to get what I need.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 9

bitbucket-25253 (3/4/2010)
--------------------------------------------------------------------------------
When a DB is backed up ... tables are backed up in their entirety. Not just changed/added rows.

Sorry about that ... just assumed (and you know what that means -- (makes an a$$ out of me) that your ERP would not do that as a restore is not quite simple -- but then again maybe they do and charge extra for helping you.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

adams.squared SSCrazy Points: 2675 More actions · Answer 10

So I think I got it. What I am going to do is create the same table in a different DB and have the data dropped then inserted from the original table every night. Just prior to that, I'll do an EXCEPT statement comparing the two tables. Whatever shows up is what has changed since the last backup. That should give me what I need.

Thanks for the help.