Post reply

Backup a line of a table if a row is updated

  • 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!

  • December 15, 2021 at 11:32 am

    #3963858

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

  • December 15, 2021 at 1:06 pm

    #3963891

    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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

  • December 15, 2021 at 11:31 pm

    #3964185

    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)
    Intro to Tally Tables and Functions

  • December 16, 2021 at 9:13 am

    #3964308

    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

  • December 16, 2021 at 6:13 pm

    #3964489

    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)
    Intro to Tally Tables and Functions

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

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