Hi all, I want to dynamically construct SQL queries that rollback if any part of the transaction fails. For instance, my app constructs the SQL needed to replace a trigger definition, such as:
begin immediate; drop trigger if exists "My Trigger"; create trigger "My Trigger" ... new definition ; commit; If the new definition fails, I want to rollback so that the old definition remains. I was under the impression that if any error occurred in the transaction that SQLite would rollback, but it seems that it only does this in some cases. According to: http://www.sqlite.org/lang_transaction.html > Response To Errors Within A Transaction > > If certain kinds of errors occur within a transaction, the transaction may or > may not be rolled back automatically. The errors that cause the behavior > include: > > • SQLITE_FULL: database or disk full > • SQLITE_IOERR: disk I/O error > • SQLITE_BUSY: database in use by another process > • SQLITE_NOMEM: out or memory > • SQLITE_INTERRUPT: processing interrupted by application request > For all of these errors, SQLite attempts to undo just the one statement it > was working on and leave changes from prior statements within the same > transaction intact and continue with the transaction. However, depending on > the statement being evaluated and the point at which the error occurs, it > might be necessary for SQLite to rollback and cancel the entire transaction. So, it seems I have to, in my application code, step through each command in the SQL transaction, note any error, then when it gets to a "commit" command, replace it instead with a "rollback". Is there a way to do this in pure SQL, without my application code having to check for errors along the way and then interrogate the SQL to look for a "commit" type line and replace it? This seems pretty error prone and convoluted. Thanks, Tom BareFeetWare -- Comparison of SQLite GUI tools: http://www.barefeetware.com/sqlite/compare/?ml _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
