How to generate SQL DML

  • 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

  • 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.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


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

  • 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

  • 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]

  • 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

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

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