I have an sqlite database with two tables that have autoincrement primary keys. I wanted those keys to be unique to the database, and not just the table.
When you use autoincrement keys in sqlite, a system table called sqlite_sequence is created with two columns "CREATE TALBE sqlite_sequence (name,seq)" where name is the table name and seq is the value of the highest key used for the table. Whenever a new row is inserted, seq is updated.
I found a way to keep that value sort of in sync with this query: "UPDATE sqlite_sequence SET seq=(SELECT MAX(seq) FROM sqlite_sequence)". That works, so I decided to add a trigger for each table, like this:
Code:
CREATE TRIGGER update_sequence
AFTER
INSERT ON table_name
BEGIN
UPDATE sqlite_sequence SET seq=(SELECT MAX(seq) FROM sqlite_sequence);
END
Unfortunately, the trigger occurs after the table is updated, but before sqlite_sequence is updated. The result is that if table_a was just updated, and the new seq is 138, table_b has a seq of 137.
I tried to create an update trigger on sqlite_sequence, but an error was thrown telling me I couldn't create a trigger on a system table.
Does anybody have an idea of how I might accomplish this? I've really been wracking my brain trying to figure this out.
sqlite triggers after insert problem
