On Wed, 18 Mar 2015 21:15:20 +0000 Simon Slavin <slavins at bigfraud.org> wrote:
> A transaction is ended when you issue the COMMIT command. A > transaction fails if any command which changes the database in it > fails due to violating the schema. If a transaction fails then all > commands in it are automatically ignored. There's no need to use > ROLLBACK. You correctly grouped commands together into a transaction > and SQL knows that if any of them fail none of them must be executed. $ sqlite3 :memory:<<EOF create table T(t int primary key); begin transaction; insert into T values (1); insert into T values (1); commit; select * from T; EOF Error: near line 4: UNIQUE constraint failed: T.t t ---------- 1 I'm not sure what you mean by "if a transaction fails". Transactions don't succeed or fail as far as SQL is concerned; statements do. Statements in the transaction that raise errors don't cause the transaction to abort or roll back. All statements within the transaction that succeed do leave the database in a changed state. The OP may be familiar with some other SQL. Other flavors of SQL (most, I'd say) support testing for @@error or somesuch in SQL, and branching accordingly. In SQLite, that's done by submitting each statement one at a time, checking the error returned to sqlite3_exec, and deciding in C whether or not to commit or rollback the transaction. It's a challenge to execute the above script from the command line. As written, it inserts one row. With "or abort" it aborts, sure enough, but still leaves one row. With "or rollback" it inserts no rows but produces "no transaction is active". About the best result is gotten by ignoring "or <anything>", writing standard SQL, and relying on -bail to exit on error, whereupon SQLite reaches end-of-input without an explicit COMMIT, and rolls back. --jkl
