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: 16167 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: 246990 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, 9 months ago by Phil Parkin.

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

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: 1004421 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: 246990 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).

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