I have searched extensively on this and I have found a lot of people asking the question but no answers that included code examples to help me understand.
I'd like to write a transaction (in sql using the command line sqlite3 interface) that performs several update statements, and if any of them fail for any reason, rolls back the transaction. The default behaviour appears to be to roll-back the statement that failed but commit the others.
This tutorial appears to advise that it's sufficient to add begin; and rollback; before and after the statements, but that's not true because I've tried it with deliberate errors and the non-error statements were definitely committed (which I don't want).
This example really confuses me because the two interlocutors seem to give conflicting advice at the end - one says that you need to write error handling (without giving any examples) whereas the other says that no error handling is needed.
My MWE is as follows:
create table if not exists accounts (
id integer primary key not null,
balance decimal not null default 0
);
insert into accounts (id, balance) values (1,200),(2,300);
begin transaction;
update accounts set balance = field1 - 100 where id = 1;
update accounts set balance = field1 + 100 where id = 2;
update accounts set foo = 23; //Deliberate error
commit;
The idea is that none of these changes should be committed.