I need to modify all the content in a table. So I wrap the modifications
inside a transaction to ensure either all the operations succeed, or none
do. I start the modifications with a DELETE statement, followed by INSERTs.
What I've discovered is even if an INSERT fails, the DELETE has still takes
place, and the database is not rolled back to the pre-transaction state.
I've created an example to demonstrate this issue. Put the following
commands into a script called EXAMPLE.SQL
CREATE TABLE A(id INT PRIMARY KEY, val TEXT);
INSERT INTO A VALUES(1, "hello");
BEGIN;
DELETE FROM A;
INSERT INTO A VALUES(1, "goodbye");
INSERT INTO A VALUES(1, "world");
COMMIT;
SELECT * FROM A;
If you run the script: "sqlite3 a.db < EXAMPLE.SQL", you will see:
SQL error near line 10: column id is not unique
1|goodbye
What's surprising is that the SELECT statement results did not show
'1|hello'.
It would appear the DELETE was successful, and the first INSERT was
successful. But when the second INSERT failed (as it was intended to)..it
did not ROLLBACK the database.
Is this a sqlite error? Or an error in my understanding of what is supposed
to happen?
Thanks
Jim
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
