What\'s the best way to keep a record of all records inserted via an SP call?

  • Goalie35

    SSC Eights!

    Points: 867

    Hey guys, so I may be overthinking this but basically, I have a bunch of stored procedures which I want to begin keeping historical track of.  I want to know which records were updated by which SP & when, and I'm just looking for the best way to do this.  Is there any built in sql functionality that can let me know which records were modified by an SP?

    Here's my issue...we have a scheduler application.  All it does is reach out to our sql server database and execute a bunch of different SP's at their scheduled execution times, in order to insert/update various data.  The only thing we track, is whether or not the SP that ran, was successfully completed, or threw an error.  We have no way of knowing which records were updated & when.

    Recently however, we've had a few incidents happen where we needed to know which records were affected & we've had no way of knowing.  So my thought process was the following:

    1. Setup a batch table to record the overall count of affected records & time the SP executed.
    2. Setup a batch details table to record the id's of any of the batch's records affected within the SP executed.

    Again, I may be overthinking this.  I'm just looking for the best way to find all records affected by the SP, so I can then insert them into my batch & batch details tables.

    Thanks

    • This topic was modified 9 months, 2 weeks ago by  Goalie35.
  • Brahmanand Shukla

    SSC Eights!

    Points: 938

    If you are using SQL 2016 or onward version then go for Temporal Tables to maintain the history. This is something new inbuilt feature as far as Audit Log/History is concerned.

    You can also explore the Change Tracking, Change Data Capture or the customized solutions like this one.

  • Phil Parkin

    SSC Guru

    Points: 244733

    Brahmanand Shukla wrote:

    If you are using SQL 2016 or onward version then go for Temporal Tables to maintain the history. This is something new inbuilt feature as far as Audit Log/History is concerned.

    You can also explore the Change Tracking, Change Data Capture or the customized solutions like this one.

    As far as I can see, none of the options you have suggested track the name of the proc which performed the update, which is a primary requirement here:

    "I want to know which records were updated by which SP & when"

    Also, the OP expresses no desire to capture the before-and-after state of the updated rows ... merely that a certain row was updated at time x by proc y.

    The best way of doing this, as far as I know, is along the lines suggested by the OP. Set up new tables to record the required information & then change all of the procs of interest to populate the new tables. There is no in-built way of getting this information.

     

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Goalie35

    SSC Eights!

    Points: 867

    There is no in-built way of getting this information.

    Thanks to both of you for your input.  I'll look into changing the procs to populate the new tables, since as Phil mentioned, there's no built in way of doing this.

    Ideally, it'd be nice to also see the before and after values of the updated fields, but I don't think this is necessary.  More importantly, we need to see which SP updated which records and when, so I'll go with the original batch & batch detail tables.

    Thanks for your help

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

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