此过程有几个步骤,这些步骤反映在数据库的各个表中：
生产 – >使用类似的东西更新到库存表
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;
上面提供了一个带有TRIGGER的日志表,如下所示：
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
最后,每次更新都应该将一行插入到我在上面添加到TRIGGER的财务表中：
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
问题出在这一行：
((SELECT TOP 1 BAL FROM FINS WHERE COMPANY = CORP ORDER BY TS DESC)- COST)
这是为了计算帐户的最新余额.但是因为CROSS APPLY将所有INSERTS视为一个批处理,所以计算是从同一个最后一个记录完成的,并且得到了一个不正确的余额数字.例：
COST BALANCE
----------------
1,000 <-- initial balance
-150 850
-220 780 <-- should be 630
解决方法
了解查询中的现有逻辑
对于满足连接条件的集合或批处理,UPDATE语句将仅触发一次触发器,Inserted语句将具有正在更新的所有记录.这是因为BATCH处理不是因为CROSS APPLY而是因为UPDATE.
在你的这个查询中
SELECT CORP,0) <> 0
对于来自外部查询的每个CORP,将返回相同的BAL.
(SELECT TOP 1 BAL FROM FINS WHERE COMPANY = CORP ORDER BY TS DESC)
话虽这么说,每当CORP =’XYZ’时,你的内部查询将被1000(你的例子中使用的值)替换
SELECT CORP,(1000- COST)
FROM inserted WHERE COALESCE(COST,0) <> 0
现在你的inserted语句包含了所有要插入的记录.所以每个记录的成本将减去1000.因此你得到了意想不到的结果.
建议的解决方案
根据我的理解,你想要计算一些累积频率类型的东西.或者上次运行总计
问题陈述的数据准备.用我的虚拟数据给你一个想法.
--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 | +-----+------+-----+-------------------------+
从上次运行余额中减去成本的替代逻辑.
--Start a recursive query to subtract balance based on cost
;WITH cte(Bal,Rno)
AS
(
SELECT t.Bal,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
产量
+-----+------+-----+ | Bal | Cost | Rno | +-----+------+-----+ | 172 | 0 | 1 | | 162 | 10 | 2 | | 150 | 12 | 3 | | 138 | 12 | 4 | | 128 | 10 | 5 | | 113 | 15 | 6 | +-----+------+-----+
