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

Question

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

Goalie35

SSC Eights!

Points: 871
More actions
December 9, 2019 at 2:28 pm

#3705988
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 5 years ago by Goalie35.

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

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

Brahmanand Shukla SSC Eights! Points: 990 More actions · Answer 1

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.

SQL Server Carpenter - Consultant & Trainer
Author of Learn T-SQL from Scratch

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

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.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Goalie35 SSC Eights! Points: 871 More actions · Answer 3

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