下一筆插入失敗，為何第一行仍留在目標？

先核來源與已訂規則

本例兩行要一同提交，各數量須有值且為正。A2 先插好，B−1 才違 CHECK。ABORT 回復失敗語句，不是回復交易內較早成功 A；捕捉錯後照提交，便留下半批。

已填假設輸入
紀錄	輸入 JSON
原例輸入	{"records":[{"id":"A","quantity":2},{"id":"B","quantity":-1}]}

下載可編輯 CSV ↓

比較兩條實際路徑

更正錯誤處理發 ROLLBACK，目標才留空；實際代碼 275 與已提交行狀態一併保留。

錯誤與更正路徑的輸出
路徑	輸出 JSON
錯誤路徑	{"error":{"code":275},"target":[{"id":"A","quantity":2}]}
更正路徑	{"error":{"code":275},"target":[]}

下載可編輯 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('CREATE TABLE target(id TEXT PRIMARY KEY,quantity INTEGER NOT NULL CHECK(quantity>0));BEGIN;');
      const error=failed(()=>{for(const r of i.records)db.prepare('INSERT INTO target VALUES(?,?)').run(r.id,r.quantity);});
      db.exec(corrected&&error?'ROLLBACK;':'COMMIT;');return {error,target:rows(db,'SELECT * FROM target ORDER BY id')};
    }finally{db.close();}
  };
const cases=[{"label":"misleading base","input":{"records":[{"id":"A","quantity":2},{"id":"B","quantity":-1}]},"expected":{"error":{"code":275},"target":[{"id":"A","quantity":2}]},"corrected":false},{"label":"corrected base","input":{"records":[{"id":"A","quantity":2},{"id":"B","quantity":-1}]},"expected":{"error":{"code":275},"target":[]},"corrected":true},{"label":"all-good batch commits both","input":{"records":[{"id":"A","quantity":2},{"id":"B","quantity":3}]},"expected":{"error":null,"target":[{"id":"A","quantity":2},{"id":"B","quantity":3}]},"corrected":true},{"label":"first statement fails","input":{"records":[{"id":"A","quantity":-2},{"id":"B","quantity":3}]},"expected":{"error":{"code":275},"target":[]},"corrected":true},{"label":"missing second quantity rolls back the batch","input":{"records":[{"id":"A","quantity":2},{"id":"B","quantity":null}]},"expected":{"error":{"code":1299},"target":[]},"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);
}

下載可執行例子 ↓

下次載入前核邊界

A/B 都有效就一起提交；首筆失敗則不試後行，回復後也留空。B 數量欠值違 NOT NULL1299，較早的 A 也一同回復。

可編輯邊界紀錄及預期輸出
情況	輸入 JSON	預期輸出 JSON
整批有效	{"records":[{"id":"A","quantity":2},{"id":"B","quantity":3}]}	{"error":null,"target":[{"id":"A","quantity":2},{"id":"B","quantity":3}]}
首句已失敗	{"records":[{"id":"A","quantity":-2},{"id":"B","quantity":3}]}	{"error":{"code":275},"target":[]}
第二行欠數量，整批回復	{"records":[{"id":"A","quantity":2},{"id":"B","quantity":null}]}	{"error":{"code":1299},"target":[]}

下載可編輯 CSV ↓

捕捉到 275，是否自動移除了前面成功語句？

查看參考答案

不是，沒有回復整批交易，A 仍在。更正失敗結果沒有已提交行。

先核來源與已訂規則

比較兩條實際路徑

下次載入前核邊界

相關資源