我正在尝试创建一个SQLite触发器来更新特定帐户code
的balance
。
accounts
表:
CREATE TABLE accounts (
year INTEGER NOT NULL,
month INTEGER NOT NULL CHECK(month BETWEEN 1 AND 12),
amount REAL NOT NULL CHECK(amount >= 0),
balance REAL,
code INTEGER NOT NULL
);
当插入新行时,我希望新行的balance
值反映OLD balance + NEW amount
。但这个触发器无法识别滞后的balance
值,我不知道为什么:
CREATE TRIGGER trg_accounts_balance
AFTER INSERT ON accounts
BEGIN
UPDATE accounts
SET balance = (
SELECT
lag(balance, 1, 0) OVER (
PARTITION BY code
ORDER BY month
) + NEW.amount
FROM accounts
)
WHERE rowid = NEW.ROWID;
END;
如果我每月插入一行,我希望我的数据看起来像:
年 | 月 | 金额余额 | 代码 | |
---|---|---|---|---|
2022 | 1 | 10.00 | 100||
2022 | 2 | >9.99 | 109.99 | 100 |
查询:
SELECT
lag(balance, 1, 0) OVER (
PARTITION BY code
ORDER BY month
)
FROM accounts
返回与表中有多少行一样多的行,SQLite选择第一行(无论是哪一行(作为结果返回,以便使用它添加NEW.amount
没有任何内容将此值链接到插入的特定行
相反,使用这个:
CREATE TRIGGER trg_accounts_balance
AFTER INSERT ON accounts
BEGIN
UPDATE accounts
SET balance = COALESCE(
(
SELECT balance
FROM accounts
WHERE code = NEW.code
ORDER BY year DESC, month DESC
LIMIT 1, 1
), 0) + NEW.amount
WHERE rowid = NEW.ROWID;
END;
子查询通过将特定code
的行降序排列并跳过最上面的一行(即新行(来返回先前插入的行
请参阅演示