Hello all,
My question concerns the order (deterministic or not) in which triggers
of the same kind (same table, same event, same position) fire.
My application is a temporal database in which each table stores the
history of one attribute of a set of entities. A view collects the last
value of each table to build the current state of these entities.
Let us simplify the problem as follows:
1. The DB comprises base tables T1(K,C1,..), T2(K,C2,..), T3(K,C3,..).
Each table comprises primary key K + column Ci + some system data.
2. SQL view T(K,C1,C2,C3) collects, for each value of K, the last value
from each base table.
3. Users update data through view T with such queries as "update T set
C1='f', C3='g' where K=12"
4. "instead of" triggers translate this update into operations on those
base tables that are affected by the update, here T1 and T3.
5. More precisely, the translation of this update proceeds in two steps:
5.1 a value "v" is computed and stored in a reference table
(typically the current_date). This operation must be performed first.
5.2 for each base table affected, an "update" then an " insert" are
performed, using value "v". These operations can be performed in any order.
Each operation is controlled by a trigger "instead of update on T". The
trigger of the first step is unconditional (no "when" clause). Each
operation of step 2 is controlled by a trigger with a filter like "when
new.C1 <> old.C1".
The problem is that the operation of step 1 MUST be performed BEFORE the
operations of step 2. So, technically, the trigger of step 1 must fire
before those of step 2.
In most DBMS (notably Oracle, DB2, PostgreSQL, SQL Server, InterBase),
firing order of similar triggers can be specified, either explicitly or
according to naming or creation time rules (no problem in MySQL: only
one trigger of a kind is allowed!).
SQLite allows multiple triggers of the same kind, which is a very good
thing, but its documentation tells nothing on the firing order issue,
which seems to be interpreted as: "firing order is arbitrary". This
considerably limits its usefulness.
The usual responses to trigger ordering problems in forums are of two kinds:
- "Your schema probably is flawed. Fix it."
- "Gather all your triggers into a single one."
No, my schema is not flawed and gathering my triggers into a single one
is impossible since it would require a programming pattern that is
missing in SQLite trigger body: "if (new.C1 <> old.C1) then <SQL
statement>". It can be simulated for updates ("update ... where ... and
new.C1 <> old.C1") but not for inserts ("insert" has no "where" clause).
It seems that, in current SQLite implementations, trigger firing order
is not quite arbitrary. In several tests I (and others) performed,
triggers appear to always fire in "inverse creation time" order.
Hence my modest proposal: wouldn't it be a nice idea to make this
unofficial order a feature of SQLite (just like DB2)? This would make
the multiple triggers of a kind much more useful as it currently are.
Thanks for your attention
Have a nice day
Jean-Luc Hainaut
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
