匯入整理值的觸發程序為何不停重跑？

先核來源與已訂規則

本例本地規則把更新後值整理為 1，並明確開 recursive_triggers。沒有門檻便連已 1 也再寫 1，反覆觸發自己，直至引擎拒語句。更正 WHEN NEW.value<>1 才只寫所需更正。

已填假設輸入
紀錄	輸入 JSON
原例輸入	{"id":1,"value":2}

下載可編輯 CSV ↓

比較兩條實際路徑

錯遞迴失敗，語句回復，原值 0 且沒有 calls。更正更新 2，只記一次 call2，停在 1。

錯誤與更正路徑的輸出
路徑	輸出 JSON
錯誤路徑	{"error":{"code":1},"target":[{"id":1,"value":0}],"calls":[]}
更正路徑	{"error":null,"target":[{"id":1,"value":1}],"calls":[{"value":2}]}

下載可編輯 CSV ↓

本地 Node.js 程序：22.23.3 或 24.14.1

import {DatabaseSync} from 'node:sqlite';
import {TextDecoder} from 'node:util';
import {inflateSync} from 'node:zlib';
import {posix} from 'node:path';
import {createHash} from 'node:crypto';
const rows=(db,sql)=>db.prepare(sql).all().map(r=>({...r}));
const failed=(action)=>{try{action();return null;}catch(e){return {code:e.errcode??e.code??e.name};}};

import assert from 'node:assert/strict';
const run=function run(i,corrected=true){
    const db=new DatabaseSync(':memory:');try{db.exec('PRAGMA recursive_triggers=ON;CREATE TABLE target(id INTEGER PRIMARY KEY,value INTEGER);CREATE TABLE calls(value INTEGER);INSERT INTO target VALUES(1,0);CREATE TRIGGER normalise AFTER UPDATE ON target '+(corrected?'WHEN NEW.value<>1 ':'')+'BEGIN INSERT INTO calls VALUES(NEW.value);UPDATE target SET value=1 WHERE id=NEW.id;END;');
      const error=failed(()=>db.prepare('UPDATE target SET value=? WHERE id=?').run(i.value,i.id));
      return {error,target:rows(db,'SELECT * FROM target'),calls:rows(db,'SELECT * FROM calls')};
    }finally{db.close();}
  };
const cases=[{"label":"misleading base","input":{"id":1,"value":2},"expected":{"error":{"code":1},"target":[{"id":1,"value":0}],"calls":[]},"corrected":false},{"label":"corrected base","input":{"id":1,"value":2},"expected":{"error":null,"target":[{"id":1,"value":1}],"calls":[{"value":2}]},"corrected":true},{"label":"already normal value needs no writeback","input":{"id":1,"value":1},"expected":{"error":null,"target":[{"id":1,"value":1}],"calls":[]},"corrected":true},{"label":"unmatched row triggers nothing","input":{"id":2,"value":2},"expected":{"error":null,"target":[{"id":1,"value":0}],"calls":[]},"corrected":true}];
for(const test of cases){
  const actual=run(test.input,test.corrected);
  assert.deepEqual(actual,test.expected,test.label);
  console.log(test.label,actual);
}

下載可執行例子 ↓

下次載入前核邊界

原本已合 1 不需寫回 call；編號未配對也無觸發活動。

可編輯邊界紀錄及預期輸出
情況	輸入 JSON	預期輸出 JSON
輸入值已合終止要求	{"id":1,"value":1}	{"error":null,"target":[{"id":1,"value":1}],"calls":[]}
目標編號未配對	{"id":2,"value":2}	{"error":null,"target":[{"id":1,"value":0}],"calls":[]}

下載可編輯 CSV ↓

值成 1 後，甚麼令更正路徑停止？

查看參考答案

WHEN 變假，不再寫回。原例結果是值 1，只有輸入 2 的一次 call。

先核來源與已訂規則

比較兩條實際路徑

下次載入前核邊界

相關資源