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?

Rosario

Logging balances to a table instead of calculating them on the fly is normally a bad design... – Snoopy

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.

Seena

‘inner query will be executed just once’ – that's an inaccurate statement. The inner query is correlated with the outer query (CORP is a column from inserted, COMPANY is from FINS). But for identical CORP values the result would be identical, of course. OP already knows that, though. – Chiseler

@AndriyM: That's a good catch. I didn't observe correlation. Correcting it. BTW what is OP? – Seena

Yes, "OP" in my comment meant "original poster". I've also seen the abbreviation used to mean "original post". (I myself never use it with that meaning, though.) – Chiseler

Answer 2

I think you can try a trigger on the Fins.

You can use IDENT_CURRENT('Table')) to take the last primary key from the table and make a select.

I think it's better than "select top 1".

To to take the last balance value, set a variable last_bal = select bal from FINS where primary_key = Ident_Current("FINS")

Rap

I had a try but I think I got it wrong. Could you show me what the trigger might look like? – Rosario

Answer 3

well

first sql is a game where it work with groups or rather "set" so always you have think about that.

if you work with a simple item is correct, it maybe be better approach

declare @myinsert table(id int identity(1,1), company VArchar(35), ts datetime, cost2 smallmoney, bal smallmoney)


insert into @myinsert(company,ts, cost2, bal)
SELECT CORP, CURRENT_TIMESTAMP, COST, 
FROM inserted WHERE COALESCE(COST,0) <> 0


declare @current int

select @current = min(id) from @myinsert

while exists(select * from @myinsert where id = @current)
begin
  INSERT INTO dbo.FINS
  (COMPANY, TS, COST2, BAL)
  SELECT COMPANY, CURRENT_TIMESTAMP, COST,
  ((SELECT TOP 1 BAL FROM FINS WHERE COMPANY = my.COMPANY ORDER BY TS DESC)- COST)
  from @myinsert my where id = @current

  select @current = min(id) from @myinsert where id > @current
end

Isaiah

Answer 4

i am not giving you exact query .For a moment forget trigger.Because you are unable to test your query . I suggest to use Output clause .This will atleast help you to construct proper query and test it. this query is running ok,(if you can use merge then that is best).

Declare @t table 
( 
  BLOC1,BLOC2,BLOC3 ,PRODUCTION ,DELTA --whatever column is require here
)  

UPDATE STOR SET
    STOR.BLOC1 = T.BLOC1,
    STOR.BLOC2 = T.BLOC2,
    STOR.BLOC3 = T.BLOC3,
    STOR.PRODUCTION = T.PROD,
    STOR.DELTA = T.DELTA
Output inserted.BLOC1 ,inserted.BLOC2,  and so on into @t
FROM BLDG B INNER JOIN STOR S
ON S.B_ID = B.B_ID
CROSS APPLY dbo.INVENTORIZE(B.B_ID) AS T;

now you have inserted value in table variable @t

 SELECT CORP, CURRENT_TIMESTAMP, COST,
 BAL,Row_Number() over(partition by company order by TS desc) RN 
FROM @t inner join FINS on COMPANY = CORP
WHERE COALESCE(COST,0) <> 0

Verify this query till here.Think of optimizing or trigger later on. I think i gave good suggestion.and I guess subtraction is not a problem.I am telling to put everything in output clause and analyze the query and test it.

you can use CTE inside trigger also but how will you test it.

  ;With CTE as
    (
      SELECT CORP, CURRENT_TIMESTAMP, COST,BAL
   ROW_NUMBER()over(ORDER BY TS DESC )rn
    FROM inserted
        inner join FINS on COMPANY = CORP

     WHERE COALESCE(COST,0) <> 0
    )
    select * from CTE --check this what you are getting

Accouter

I don't see subtraction logic in this query – Seena

my idea was to first get the query correct using output.If query is correct then putting subtraction is not a problem.First let @Greener try my way .if problem is still not solved then throw some sample data in sql fiddle . – Accouter

IMHO, you should try looking into subtraction logic as well. Since its a tricky piece of code. He is able to come very close to rest of the section. – Seena

Answer 5

Something like that, Isn't complete.

CREATE TRIGGER trgrCYCLE
ON STOR
FOR UPDATE
AS
begin

declare @last_bal int

declare @company varchar(50)
declare @ts  --type
declare @cost  int
declare @bal  --type
--etc whatever you need

select @company = company, @ts= ts , @cost = cost , @bal = bal from INSERTED

 --others selects  and sets

set @last_bal = select bal from dbo.FINS where you_primary_key = IDENT_CURRENT('FINS'))

set @last_bal = @last_bal - @cost

Insert INTO FINS (company, ts, cost2, bal) VALUES (@company, @ts, @cost, @last_bal) where --your conditions


end

Rap

Answer 6

If, similar to @Shantanu's method, you could associate a sequence with inserted, the virtual table associated with the trigger you could do this by subtracting all the COSTs that come before the current record.

This could be accomplished by adding a rowversion to STOR, which will be updated automatically with each delete.

Then instead of:

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

make the rowversion RV, and:

(SELECT SUM(X.B) FROM
      (SELECT TOP 1 BAL B 
           FROM FINS 
           WHERE COMPANY = CORP
           ORDER BY TS DESC
       UNION
       SELECT -COST B 
           FROM inserted ii
           WHERE ii.RV >= i.RV AND ii.CORP = i.CORP
       ) AS X)
    FROM inserted i WHERE COALESCE(COST,0) <> 0

Should do what you want. You could conceivably do this with a timestamp that was more find-grained than CURRENT_TIMESTAMP which, I believe, goes down only to seconds but that requires you update it in the UPDATE statement. The rowversion may cause problems with your STOR insert statements.

Vogue