sql - 使用触发器计算SQLite表中的运行总计

Question

如何创建SQLite触发器以计算“实际”表上的运行总计？以下SQL代码应更新AccountBalances表，以使Balance列从1、2、3，...行计数开始计数。但是，即使我打开recursive_triggers，触发器也只会更新第二行。下面的结果是行1 = 1，行2 = 2，其后的行为空。

CREATE TEMP TABLE "AccountBalances" (
  "Id" INTEGER PRIMARY KEY, 
  "DateId" INT,
  "AccountId" INT,
  "AccountCurrAmount" REAL,
  "Balance" REAL);

INSERT INTO "AccountBalances" 
  (DateId, AccountId, AccountCurrAmount)
  SELECT DateId, AccountId, Sum(AccountCurrAmount) 
    FROM Actual 
GROUP BY DateId, AccountId 
ORDER BY AccountId, DateId;

CREATE TRIGGER UpdateAccountBalance AFTER UPDATE ON AccountBalances
BEGIN
 UPDATE AccountBalances 
    SET Balance = 1 + new.Balance 
  WHERE Id = new.Id + 1;
END;

PRAGMA recursive_triggers = 'on';

UPDATE AccountBalances 
   SET Balance = 1 
 WHERE Id = 1

Answer 1

请检查SQLITE_MAX_TRIGGER_DEPTH的值。可以将其设置为1而不是默认值1000吗？
请检查您的SQLite版本。在3.6.18之前，不支持递归触发器。


请注意，以下内容对我有用100％还可以

删除表“ AccountBalances”

CREATE TEMP TABLE "AccountBalances" (
  "Id" INTEGER PRIMARY KEY, 
  "Balance" REAL);

INSERT INTO "AccountBalances" values (1,0)
INSERT INTO "AccountBalances" values (2,0);
INSERT INTO "AccountBalances" values (3,0);
INSERT INTO "AccountBalances" values (4,0);
INSERT INTO "AccountBalances" values (5,0);
INSERT INTO "AccountBalances" values (6,0);

CREATE TRIGGER UpdateAccountBalance AFTER UPDATE ON AccountBalances
BEGIN
 UPDATE AccountBalances 
    SET Balance = 1 + new.Balance 
  WHERE Id = new.Id + 1;
END;

PRAGMA recursive_triggers = 'on';

UPDATE AccountBalances 
   SET Balance = 1 
 WHERE Id = 1

select * from "AccountBalances";

结果：

Id  Balance
1   1
2   2
3   3
4   4
5   5
6   6