Post reply

Insert Insert for each Update

  • December 5, 2005 at 8:43 am

    #102970

    Hi,

    I have an update statement in my stored procedure where I am updating the dates of a set of records satisfying certain criteria in the Where clause:

    UPDATE LabEquipmentState

       SET StateStartDate = StateStartDate + @DateDiff,

        StateEndDate = StateEndDate + @DateDiff

       WHERE LabEquipmentId = @LabEquipmentId AND

        TestRequestStateId = 5 AND

        StateStartDate > @CurrStartDate  

    Now, I want to create a History table where I want to store all the records that were changed. So before updating these records I want to Insert the original record in the History table.

    So, what I want to do is for every update ocurring I want to make an Insert in another History table. So how do I write the query for that ?? I would really appreciate any help on this as this is very urgent.

    Thanks,

    Snigdha

  • December 5, 2005 at 9:12 am

    #607727

    Well 2 options come to my mind:

    Begin an explicit transaction while updating the table. In the same transaction, write an insert statement to the history table. In this case, the insert into the history table will happen only when an update is a success.

    2nd Option: Try triggers.

  • December 5, 2005 at 9:23 am

    #607729

    Hey,

    Never thought of triggers..hmm seems to be a good idea. I think I will go ahead and try the trigger option.

    Thanks,

    Snigdha

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

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