SQLite error: cannot start a transaction within a transaction with very basic tables

Question

I am brand new to SQL, and I am learning on an SQLite editor. So I create a couple of very simple tables. This code is straight from Linkedin learning "SQL essential training", and I am using the recommended SQLite editor.

CREATE TABLE widgetInventory(
    id INTEGER PRIMARY KEY,
    description TEXT,
    onhand INTEGER NOT NULL);

CREATE TABLE widgetSales(
    id INTEGER PRIMARY KEY,
    inv_id INTEGER,
    quan INTEGER,
    price INTEGER);

Then I update widgetInventory with some data:

INSERT INTO widgetInventory (description, onhand) VALUES ('rock', 25);
INSERT INTO widgetInventory (description, onhand) VALUES ('paper', 25);
INSERT INTO widgetInventory (description, onhand) VALUES ('scissors', 25);

Next, I want to update the widgetSales table with a sale, and update the widgetInventory table to record the reduction of onhand.

BEGIN TRANSACTION;
INSERT INTO widgetSales (inv_id, quan, price) VALUES (1,5,500);
UPDATE widgetInventory SET onhand = (onhand-5) WHERE id = 1;
END TRANSACTION;

I am not understanding why this gives me an error when I run it, as it is exactly as it is in the lesson.

[06:18:04] Error while executing SQL query on database 'test': cannot start a transaction within a transaction

But, I can run the INSERT and UPDATE lines separately, and they do what I want them to do.

Answer 1

Apparently, running - END TRANSACTION; - before running the entire transaction appears to work.

I think that somehow, SQL thinks that a transaction is already occurring. Though, I'm not sure where exactly. So to stop it, you have to end the transaction first before proceeding with the course.

Answer 2

In the SQLite Editor, you may have to delete or comment out all of the code before and after these two transactions.

BEGIN TRANSACTION;
  INSERT INTO widgetSales ( inv_id, quan, price ) VALUES ( 1, 5, 500 );
  UPDATE widgetInventory SET onhand = ( onhand - 5 ) WHERE id = 1;
END TRANSACTION;

BEGIN TRANSACTION;
  INSERT INTO widgetInventory ( description, onhand ) VALUES ( 'toy', 25 );
ROLLBACK;

Otherwise it won't execute the transaction.

Other than that, there is probably an error written in somewhere. Copying and pasting in the .txt file didn't give me that transaction error and could execute the transaction normally.