select last_insert_rowid() doesn't account for ROLLBACK on transaction

[pydanny]

We probably don't want to use the SQLIte function last_insert_rowid() to get the last_pk. Per the docs on last_insert_rowid, this is expected behavior:

For the purposes of this routine, an INSERT is considered to be successful even if it is subsequently rolled back.

---

Get last rowid

SELECT last_insert_rowid();

0

Now do the transaction and check the row id:

BEGIN TRANSACTION;
INSERT INTO users (username) VALUES ('puppy');
SELECT last_insert_rowid();

1

Roll the transaction back and check the table and rowid:

ROLLBACK;
SELECT last_insert_rowid();

1

[pydanny]

@audreyfeldroy's fix from yesterday:

BEGIN IMMEDIATE TRANSACTION;

INSERT INTO your_table (column1, column2, ...) VALUES (value1, value2, ...);
SELECT last_insert_rowid() AS last_inserted_id;

COMMIT;