Sequential SQL inserts when triggered by CROSS APPLY

Question

This process has several steps which are reflected in various tables of a database:

Production --> UPDATE to the inventory table using something like

UPDATE STOR SET
    STOR.BLOC1 = T.BLOC1,
    STOR.BLOC2 = T.BLOC2,
    STOR.BLOC3 = T.BLOC3,
    STOR.PRODUCTION = T.PROD,
    STOR.DELTA = T.DELTA
FROM BLDG B INNER JOIN STOR S
ON S.B_ID = B.B_ID
CROSS APPLY dbo.INVENTORIZE(B.B_ID) AS T;

The above feeds a log table with a TRIGGER like this:

CREATE TRIGGER trgrCYCLE
ON STOR
FOR UPDATE
AS
INSERT INTO dbo.INVT
    (TS, BLDG, PROD, ACT, VAL)
    SELECT CURRENT_TIMESTAMP, B_ID, PRODUCTION,
        CASE WHEN DELTA < 0 THEN 'SELL' ELSE 'BUY' END,
        DELTA
    FROM inserted WHERE COALESCE(DELTA,0) <> 0

And finally, every update should INSERT a row into a financials table which I added to the TRIGGER above:

INSERT INTO dbo.FINS
    (COMPANY, TS, COST2, BAL)
    SELECT CORP, CURRENT_TIMESTAMP, COST,
    ((SELECT TOP 1 BAL FROM FINS WHERE COMPANY = CORP ORDER BY TS DESC)- COST)
    FROM inserted WHERE COALESCE(COST,0) <> 0

The problem is with this line:

((SELECT TOP 1 BAL FROM FINS WHERE COMPANY = CORP ORDER BY TS DESC)- COST)

which is meant to calculate the latest balance of an account. But because the CROSS APPLY treats all the INSERTS as a batch, the calculation is done off of the same last record and I get an incorrect balance figure. Example:

 COST    BALANCE
----------------
          1,000   <-- initial balance
 -150       850
 -220       780   <-- should be 630

What would be the way to solve that? A trigger on the FINS table instead for the balance calculation?

Answer 1

Understanding existing logic in your query

UPDATE statement will fire a trigger only once for a set or batch satisfying join conditions, Inserted statement will have all the records that are being updated. This is because of BATCH processing not because of CROSS APPLY but because of UPDATE.

In this query of yours

   SELECT CORP, CURRENT_TIMESTAMP, COST,
    ((SELECT TOP 1 BAL FROM FINS WHERE COMPANY = CORP ORDER BY TS DESC)- COST)
    FROM inserted WHERE COALESCE(COST,0) <> 0

For each CORP from an Outer query, same BAL will be returned.

(SELECT TOP 1 BAL FROM FINS WHERE COMPANY = CORP ORDER BY TS DESC)

That being said, your inner query will be replaced by 1000(value you used in your example) every time CORP = 'XYZ'

   SELECT CORP, CURRENT_TIMESTAMP, COST, (1000- COST)        
    FROM inserted WHERE COALESCE(COST,0) <> 0

Now your inserted statement has all the records that are being inserted. So every record's cost will be subtracted by 1000. Hence you are getting unexpected result.

Suggested solution

As per my understanding, you want to calculate some cumulative frequency kind of thing. Or last running total

Data Preparation for problem statement. Used my dummy data to give you an idea.

--Sort data based on timestamp in desc order
SELECT PK_LoginId AS Bal, FK_RoleId AS Cost, AddedDate AS TS
, ROW_NUMBER() OVER (ORDER BY AddedDate DESC) AS Rno 
INTO ##tmp 
FROM dbo.M_Login WHERE AddedDate IS NOT NULL


--Check how data looks
SELECT Bal, Cost, Rno, TS FROM ##tmp

--Considering ##tmp as your inserted table, 
--I just added Row_Number to apply Top 1 Order by desc logic 

+-----+------+-----+-------------------------+
| Bal | Cost | Rno |           TS            |
+-----+------+-----+-------------------------+
| 172 |   10 |   1 | 2012-12-05 08:16:28.767 |
| 171 |   10 |   2 | 2012-12-04 14:36:36.483 |
| 169 |   12 |   3 | 2012-12-04 14:34:36.173 |
| 168 |   12 |   4 | 2012-12-04 14:33:37.127 |
| 167 |   10 |   5 | 2012-12-04 14:31:21.593 |
| 166 |   15 |   6 | 2012-12-04 14:30:36.360 |
+-----+------+-----+-------------------------+

Alternative logic for subtracting cost from last running balance.

--Start a recursive query to subtract balance based on cost
;WITH cte(Bal, Cost, Rno)
AS
(
    SELECT t.Bal, 0, t.Rno FROM ##tmp t WHERE t.Rno = 1
    UNION ALL
    SELECT c.Bal - t.Cost, t.Cost, t.Rno FROM ##tmp t 
      INNER JOIN cte c ON t.RNo - 1 = c.Rno
)
SELECT * INTO ##Fin FROM cte;

SELECT * FROM ##Fin

Output

+-----+------+-----+
| Bal | Cost | Rno |
+-----+------+-----+
| 172 |    0 |   1 |
| 162 |   10 |   2 |
| 150 |   12 |   3 |
| 138 |   12 |   4 |
| 128 |   10 |   5 |
| 113 |   15 |   6 |
+-----+------+-----+

You have to tweet your columns little bit to get this functionality into your trigger.