SQLite Forum

RAISE(ROLLBACK,...) giving 'no transaction error'
Login

RAISE(ROLLBACK,...) giving 'no transaction error'

(1) By anonymous on 2020-09-14 20:55:43 [source]

If inside a trigger I use RAISE(ROLLBACK,...) I get an error that there is no transaction. That's correct.

If I use ABORT instead of ROLLBACK my understanding is it will not rollback a transaction, only abort the current statement.

So, how do I cope with both at the same time?

I need a trigger that will rollback the whole transaction when run from inside a transaction but give no error (and abort just the statement) when run from outside an explicit transaction.

(2) By Keith Medcalf (kmedcalf) on 2020-09-14 21:29:15 in reply to 1 [link] [source]

There is no builtin SQL function that can tell you whether you are inside an explicit transaction.

I suppose you could write a UDF that used the sqlite3_get_autocommit API (https://sqlite.org/c3ref/get_autocommit.html) to return whether or not you were in autocommit or an explicit transaction.

However, why would you want to rollback a transaction if you do not know that there is a transaction in progress? Surely the application issuing the command knows whether or not a transaction is in progress and can respond appropriately to an ABORT error?

(3) By Keith Medcalf (kmedcalf) on 2020-09-14 21:45:44 in reply to 1 [link] [source]

As an aside I note that on the current trunk there is no such situation.

I would note that since it is impossible for a "trigger" to fire other than within a transaction context, that ROLLBACK would always be successful whether or not the transaction scope being rolled back was merely the current statement (as in an implicit transaction) or some other explicit transaction context.

I have no idea when or where it was changed. Richard may have info on this.

What version of SQLite3 are you observing the behaviour of which you speak?

SQLite version 3.34.0 2020-09-14 08:14:15
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x(x);
sqlite> create trigger ix before insert on x begin select raise(ROLLBACK, 'Transaction Rollback'); end;
sqlite> insert into x values (1);
Error: Transaction Rollback
sqlite> drop trigger ix;
sqlite> create trigger ix after insert on x begin select raise(ROLLBACK, 'Transaction Rollback'); end;
sqlite> select * from x;
sqlite> insert into x values (1);
Error: Transaction Rollback
sqlite> select * from x;
sqlite> begin;
sqlite> insert into x values (1);
Error: Transaction Rollback
sqlite> rollback;
Error: cannot rollback - no transaction is active

(4) By anonymous on 2020-09-15 07:13:25 in reply to 2 [link] [source]

There is no builtin SQL function that can tell you whether you are inside an explicit transaction

I guess there could be now, given the upcoming and unreleased https://www.sqlite.org/draft/c3ref/txn_state.html API.

(5) By Dan Kennedy (dan) on 2020-09-15 13:43:14 in reply to 1 [link] [source]

If inside a trigger I use RAISE(ROLLBACK,...) I get an error that there is no transaction. That's correct.

I don't think it is correct. Any statement that causes a trigger to fire should open an implicit transaction if there is not one already open.

Are you doing something odd in another user function called by the same statement? An explicit ROLLBACK or similar?

Otherwise, can you post an SQL script that reproduces the problem?

Thanks, Dan.