Updating data with recording old values - thought process

Question

Updating data with recording old values - thought process

ucltech

SSC Veteran

Points: 240
More actions
April 4, 2020 at 1:17 pm

#3740416

Client has a projections table that sales reps can update on their own. They want to record any changes to the table. Currently it's a simple salesname,company,item.year,q1,q2,q3,q4 table and the current process, instead of searching/updating we just delete and re-add the changes. They would like to record, if the record exists, the previous value the sales rep entered along with their new projection value.
Other than a stored proc to first search for, grab if exists and insert into an archive table - there is no other trick solution?
It's not 2019 so we can't implement change data capture.

Viewing 7 posts - 1 through 6 (of 6 total)

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

frederico_fonseca SSCoach Points: 16011 More actions · Answer 1

first - CDC is available since 2008 at lest - Maybe you are talking about temporal tables?

For recording the changes you can implement a delete trigger - and for each deleted record insert it into the archive table.

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

frederico_fonseca wrote:

Maybe you are talking about temporal tables?

And they have been available since SS 2016.

The best method depends on what is driving the requirement. Is this for reporting, auditing or something else?

This reply was modified 4 years ago by Phil Parkin.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

ucltech SSC Veteran Points: 240 More actions · Answer 3

I thought CDC was only a 2019+ concept, testing a delete trigger now.

ucltech SSC Veteran Points: 240 More actions · Answer 4

It's more for auditing. If a sales rep adjusts their projection for a given company, they want to see what the previous projection was even if it ends up with 1 primary projection and 11 archive ones.

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 5

This is a nice narrow table. You can do a lot of what you ask for auto-magically in 2017 using System-Versioned Temporal Tables. It will not only record the history but, since it's based on SCD Type 6, you can "replay" history at any point in time quite easily. Please see the following "top level" article on the subject. The only thing that it can't do is tell you who or what made the change. If you need that, then doing something similar in a homegrown fashion with a trigger will work a treat for you. This may be especially important if sales reps can make changes to other rep's data.

https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15

--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)

Phil Parkin SSC Guru Points: 246693 More actions · Answer 6

The trigger/audit table solution sounds like the best fit to me. It's been done many times before and there are loads of articles around on this ... I think I've even seen an article somewhere which included a script to generate all of the SQL code for you (to CREATE the audit table and the trigger).

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.