My problem is the concept of outer and inner transactions.
What I anticipated: BEGIN signals the start of the transaction.
To avoid the error I need to move save point "B".
The outer transaction is A
The inner transaction is B
And the outer transaction doesn't start with the begin instruction.
1 SAVEPOINT "A";
2 CREATE TABLE T1 (X TEXT NOT NULL DEFAULT "");
3
4 INSERT INTO T1 (X)
5 VALUES ("A"), ("B");
6 SELECT "What was inserted?";
7 SELECT * FROM T1;
8 BEGIN;
9 SAVEPOINT "B";
10 INSERT INTO T1 (X)
11 SELECT "C";
12 SELECT "What was inserted?";
13 SELECT * FROM T1;
14 ROLLBACK TO "B";
15 SELECT "What was inserted?";
16 SELECT * FROM T1;
17 END;
18 DELETE FROM T1;
19 SELECT "What was inserted?";
20 SELECT * FROM T1;
21 ROLLBACK;
22 SELECT "What was inserted?";
23 SELECT * FROM T1;
24
And the result is:
What was inserted?
A
B
Error: near line 8: cannot start a transaction within a transaction
What was inserted?
A
B
C
What was inserted?
A
B
What was inserted?
Error: near line 21: cannot rollback - no transaction is active
What was inserted?
Line 8 The error wasn't solved.
Line 17 tells me that BEGIN doesn't start a new transaction because it failed.
So what is the use of a BEGIN in a transaction?
But wait! Is begin short for begin transaction? That would explain a lot.