Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

I have a table with a Balance column of type decimal(18,0).

I want to get updates to its Balance field and reflect the changes in the parents of the updated row.

Currently I can easily subtract parent values by selecting from deleted table and add to parent values by selecting from inserted table in two different queries. Since the updates will trigger an other update and so the concept of recursive triggers (enabled in my database) comes to scene, I want to have the minimum number of updates.

Is the following the best way to merge inserted and deleted table and to get the real difference in Balance field?

Update MyTable
Set Balance += updated.Price
From (  
    Select  IsNull(inserted.ParentID, deleted.ParentID) As ParentID, 
            (IsNUll(inserted.Balance,0) - IsNull(deleted.Balance,0))  As Price 
            From inserted
            Full join deleted
            on inserted.ID = deleted.iD
            WHERE IsNull(inserted.ParentID, deleted.ParentID) IS NOT NULL
) As updated
INNER JOIN MyTable
ON MyTable.ID = updated.ParentID
Where updated.ParentID IS NOT NULL

For example changing Balance from 1000 to 800 should yield to -200 change in parent Balance fields.

Also, my code fires the trigger one extra time, so that for an update in level 3 I get 4 updates (trigger-firing) instead of 3.

share|improve this question
    
what RDMS are you using? is this SQL Server? –  Malachi Jul 13 at 13:46
    
@Malachi, yes MS SQL Server –  Mohsen Afshin Jul 13 at 17:49
    
What version of sql server are you using ? –  Kin Aug 17 at 22:24
    
@Kin SQL Server 2012 –  Mohsen Afshin Aug 18 at 5:09

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.