Read the source and its declared rule
This original local rule normalises an updated target value to 1. recursive_triggers is explicitly ON. An unconditional trigger updates value 1 even when it is already 1, recursively triggering itself until the engine rejects the statement. Corrected WHEN NEW.value<>1 performs only the required writeback.
| Record | Input JSON |
|---|---|
| Base input | {"id":1,"value":2} |
Compare the actual paths
Bad recursion fails and reverses the statement, leaving original 0 and no calls. Corrected update 2 records one call 2, settles at 1 and terminates.
| Path | Output JSON |
|---|---|
| Misleading path | {"error":{"code":1},"target":[{"id":1,"value":0}],"calls":[]} |
| Corrected path | {"error":null,"target":[{"id":1,"value":1}],"calls":[{"value":2}]} |
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);
}
Check boundaries before the next load
Already-normal 1 needs no writeback call. Updating an unmatched id causes no trigger activity.
| Case | Input JSON | Expected output JSON |
|---|---|---|
| Already normal incoming value | {"id":1,"value":1} | {"error":null,"target":[{"id":1,"value":1}],"calls":[]} |
| Unmatched target id | {"id":2,"value":2} | {"error":null,"target":[{"id":1,"value":0}],"calls":[]} |
What terminates the corrected path after value becomes 1?
Show the worked answer
The WHEN condition becomes false. No further writeback runs; the base result is value 1 with one call for incoming 2.