logo标签列表

由CROSS APPLY触发时的顺序SQL插入

sql-servert-sqlcross-apply
8

这个过程包含多个步骤，涉及数据库中的不同表格：

生产 --> 使用类似以下语句对库存表执行 UPDATE

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

最后，每次更新应该插入一行数据到我在上面添加的财务表中：

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

有什么方法可以解决这个问题？在FINS表上触发器来进行结算平衡吗？

将余额记录到表中而不是即时计算通常是一种不良设计... - JNK
6个回答
2

了解查询中的现有逻辑

UPDATE语句仅在满足联接条件的一组或批次上触发trigger，插入语句将具有正在更新的所有记录。这是由于批处理而不是由于CROSS APPLY，但是由于UPDATE。

在您的此查询中：

   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

对于外部查询中的每个组织（CORP），都将返回相同的BAL。
(SELECT TOP 1 BAL FROM FINS WHERE COMPANY = CORP ORDER BY TS DESC)

话虽如此，每当CORP = 'XYZ'时，您的内部查询将被1000（您在示例中使用的值）替换。

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

现在您插入的语句包含所有要插入的记录。因此，每个记录的成本都将减少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, 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

输出

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

您需要微调您的列，以便将此功能添加到触发器中。

1
“内部查询只会执行一次” - 这是不准确的说法。内部查询与外部查询相关联（CORP来自inserted，COMPANY来自FINS）。但对于相同的CORP值，结果当然是相同的。虽然OP已经知道这一点。 - Andriy M
@AndriyM：很棒的发现。我没有观察到相关性。我会纠正它。顺便问一下，OP是什么意思？ - Shantanu Gupta
是的，在我的评论中，“OP”指的是“原帖作者”。我也见过这个缩写被用来表示“原始帖子”。（不过，我自己从未使用它表示那个意思。） - Andriy M
0

好的

首先，SQL 是一种与组或“集合”一起工作的游戏，因此您始终需要考虑这一点。

如果您使用简单项，则是正确的，这可能是更好的方法。

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
0

我不会给你精确的查询语句。暂时忘记触发器，因为你无法测试你的查询语句。 我建议使用Output子句。这至少可以帮助你构建正确的查询语句并进行测试。 如果你能使用Merge，那就最好了。

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;

现在您已经在表变量 @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

验证这个查询到这里。考虑后续的优化或触发器。 我认为我提供了很好的建议，而且我猜减法不是问题。我建议将所有内容放在输出子句中，并分析查询并测试它。

你也可以在触发器中使用CTE，但你如何测试呢？

  ;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
我在这个查询中没有看到减法逻辑。 - Shantanu Gupta
我的想法是首先使用输出正确地获取查询。如果查询正确，那么减法就不是问题了。首先让@Greener尝试我的方法。如果问题仍未解决，则在SQL Fiddle中抛出一些示例数据。 - KumarHarsh
我认为你也应该尝试研究减法逻辑，因为它是一段棘手的代码。他能够接近其余部分的水平。 - Shantanu Gupta
0

类似这样的，还不完整。

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
0

如果像@Shantanu的方法一样，您可以将序列与插入相关联，与触发器相关联的虚拟表，您可以通过减去当前记录之前的所有COST来实现这一点。

这可以通过向STOR添加一个rowversion来完成，每次删除时都会自动更新。

然后，不是：

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

使rowversion RV，并且：

(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

应该做你想要的。你可以用比CURRENT_TIMESTAMP更细粒度的时间戳来实现，但这需要在UPDATE语句中更新它。行版本可能会导致你的STOR插入语句出现问题。

0

我认为你可以在Fins上尝试触发器。

您可以使用IDENT_CURRENT('Table'))从表中获取最后一个主键并进行选择。

我认为这比“select top 1”更好。

要获取最后一个余额值，请设置一个变量last_bal = select bal from FINS where primary_key = Ident_Current("FINS")。

我尝试过了，但我认为我做错了。你能给我展示一下触发器可能是什么样子吗？ - greener
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接