How to generate SQL DML

Question

How to generate SQL DML

trevornews

Old Hand

Points: 390
More actions
December 7, 2004 at 7:49 pm

#60892

I have this peculiar request. We have a library which all developers use to connect to the Production databases. During the course of their interaction with the DB, any Update or Delete statement requires me to take the current state of the Row(s) based on the where clause from the Update/Delete statement and generate all the SQL and store this as a backup, either as a Text File or SQL Table.
The reason for this is that sometimes, users from different departments update certain records that can trigger unwanted results causing Production to be down causing a huge loss. We then go back to trace the problem but the previous state of the Record is lost and then we need to get all the deprtments together to find out what the Row(s) should represent. Sometimes they are irrevisable and then we go back to the backup. If we have these statements and the Tehcnicians who are on duty can get the data back into the database so that we can aviod the loss due to downtime. Hence this backup procedure is perfectly acceptable. We do not want to go through the database (in terms of Triggers) for achieving this. Any other ways of programatically doing this. Either through VB or otherwise.
Best Regards,
Trevor Benedict R

Best Regards,

Trevor Benedict R
Microsoft Certified Solution Developer

Viewing 5 posts - 1 through 4 (of 4 total)

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

Jeff Moden SSC Guru Points: 1003867 More actions · Answer 1

Not sure why you wouldn't want to use a set based Trigger for this... it's a tried and true method and will run raster than anything else you could throw at the problem.

I don't personally know of a method, other than using a Trigger, to do what you ask. That doesn't mean there isn't one, I just haven't heard of one. Perhaps something with the Log file may be in order but, again, it's bound to be slower than using a Trigger to store changed/deleted records in an "Audit Log" table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

trevornews Old Hand Points: 390 More actions · Answer 2

Thanks for the feedback. I know that we could have used a Trigger, but in order to maintain these triggers, we need to go through an extra process, which we would better aviod and also to keep this at the application level. Maybe my specific requirement.

Trevor Benedict R

Best Regards,

Trevor Benedict R
Microsoft Certified Solution Developer

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Not sure if I understand you exactly, but see, if this helps:

http://www.karaszi.com/sqlserver/info_generate_script.asp

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Russell Shilling SSCarpal Tunnel Points: 4349 More actions · Answer 4

There are a few utilities which will let you view the transaction logs. You would be able to find the change, and see the previous values using that. There are also a couple of tools that will let you selectively undo operations that are stored in the log. Look around this site for vendor ads -- I'm sure you'll find something.

So long, and thanks for all the fish,

Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3