Merge inserted and deleted tables in SQL trigger

Question

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.

This is on SQL Server 2012.

Answer 1

For me it is not exactly clear what you are trying to achieve, but it looks like propagating a balance change up into some tree (some chart of accounts, maybe?).

First, using triggers like this is very convoluted and error prone. Max recursion level for nested triggers is 32 and it is very hard to trace what it is going on.

Maybe the following approach might be useful for you: create a temporary table based on your data to update (you may have multiple updates at once)

ID
ParentID
LevelNo -- should contain the level in the tree
OriginalBalance
NewBalance

Data can be populated using a recursive CTE, if update is touching only one node and you want to operate on that branch only.

For each level = LevelNo to 1 Update parent with balance difference

This suggestion works only if your structure is a tree. If you provide us more details (table structure etc), we may come with a more concrete solution.

Answer 2

I don't fully understand the nature of the problem you're solving with this trigger, so can't critique the general approach.

However, I can see a number of ways you could improve on the existing code to make it clearer, without fundamentally changing the approach.

1. Use a CTE (or a temp table), rather than a sub query, for updated. It's clearer and easier to follow. I doubt it'll affect performance one way or the other in this situation.

2. Start your main query with MyTable - because that's the main row set here, and the thing you're updating. Then join to the updated set, rather than the other way around.

3. Use table aliases e.g. ...from inserted as I..., on I.ID = D.ID.

4. Lose the final WHERE clause. You're inner joining to updated.ParentId so it can't be NULL (inner joins omit non matching rows).