Transaction level triggers?
I am building a database of Facts that can be asserted (i.e created) or retracted (i.e. tombstones as not longer valid), but that are never deleted. I would like to record the ids of all facts asserted or retracted within a Sqlite transaction. My plan of campaign was to have a separate Transact table and save its id as a foreign key in the Fact table.
CREATE TABLE Transact ( tx_number INTEGER PRIMARY KEY AUTOINCREMENT, tx_timestamp TEXT NOT NULL DEFAULT (STRFTIME('%Y-%m-%d %H:%M:%f', 'NOW', 'localtime')), user TEXT NOT NULL );
CREATE TABLE Fact_p ( uuid INTEGER NOT NULL DEFAULT LOWER( HEX( RANDOMBLOB( 16 ))), asserted INTEGER NOT NULL REFERENCES Transact (tx_number), retracted INTEGER REFERENCES Transact (tx_number) -- more stuff! );
Applications access Fact_p through a Fact view that has INSTEAD OF triggers; INSERT can thus set "asserted" to the current tx_number, DELETE "retracted" to the current tx_number. UPDATE is just a DELETE followed by an INSERT. The Fact view is in any event necessary to filter out retracted Facts (and to be able to display the state of the database as at any given time).
Any given interaction with the database involves asserting and retracting a number of Facts, all of which must share the same tx_number. This, in turn, implies adding a new row to the Transact table as a precursor to, but within, every database transaction. It needs to be within the transaction so that if anything fails and the whole transaction rolls back, the new row is not created in the Transact table.
It would be relatively easy if I could attach a trigger to the BEGIN statement, the trigger could then INSERT into Transact and the INSTEAD OF triggers attached to Fact could simply SELECT the tx_number of the most recent created row. Attaching a trigger to a transaction, however, does not seem possible.
Can anyone suggest a work around? For various reasons I'd prefer to have the database do the work rather than application code.
But the transaction could be implicit. Or involving other tables that don't need the Transact row.
Sounds like what you are really asking, is for statement-level triggers IMHO, rather than Transaction-level triggers,
which can thus be table-specific, and insert the row if needed (although how to know if the row is needed? Implies having a built-in for the current transaction somehow).
I've long wished for statement-level triggers in SQLite, as opposed to
FOR EACH ROW ones. Been using the former in Oracle and PostgreSQL, and miss them in SQLite.