Backup a line of a table if a row is updated

Question

Post reply

Backup a line of a table if a row is updated

jbeclapez

SSC-Addicted

Points: 405
More actions
December 15, 2021 at 11:01 am

#3963840

Hi there!
I have an SQL table called ORIGIN with 5 fields.
Each time one of the field in changing, I would like this line to be copied to another table called BACKUP.
What i am trying to achieve is to have a simple way to do backup that is accessible to the end user.
I do not know how I can achieve that using SQL. I am not even sure it is possible.
Thanks for your advices!

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

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

Ken McKelvey SSCoach Points: 18842 More actions · Answer 1

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

I'm with Ken. Temporal tables is probably the better solution. However, you also can look at setting up an UPDATE trigger as shown at the link. Be cautious and test this though. Triggers are a good way to hurt performance if they're not implemented judiciously.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

Heh... Temporal Tables. Reminds me of String_Split(). Fantastic idea but they left a part out... like who or what made the update. 🙁

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 4

Jeff Moden wrote:

Heh... Temporal Tables. Reminds me of String_Split(). Fantastic idea but they left a part out... like who or what made the update. 🙁

Indeed, Jeff, that operational metadata should be built into the object.

i.e. add an extra column to document who inserted/modified the row ( and how do you treat deletes ? )

btw datetime related operational metadata is handled by the temporal construct

e.g.

...
, Row_start_ts DATETIME2 GENERATED ALWAYS AS ROW START
, Row_end_ts DATETIME2 GENERATED ALWAYS AS ROW END
, PERIOD FOR SYSTEM_TIME ( Row_start_ts , Row_end_ts )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

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

Yeah! That last part forms a beautiful Type 6 SCD (Slowly Changing Dimension) but it's missing the "who dunnit" column. Adding one will either put the screws to the Temporal Table or require you to have the app update an LMB (LastModifiedBy) column or you need yet another trigger and that brings me to another serious short coming. "After triggers" cause double the work and "Instead of" triggers are a huge PITA to write correctly for UPDATEs. Why oh why won't Microsoft create a real "Before Trigger" like Oracle has (which I found tremendously useful back when I worked with Oracle).

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