> 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).
Maybe so. But, INSERT can accept data rows from a SELECT statement which
both does have a WHERE clause and is aware of the trigger body variables.
https://sqlite.org/lang_insert.html
Taking your example NEW.C1 and OLD.C1, statements like INSERT INTO ....
SELECT .... [FROM ....] WHERE <NEW.C1 condition> AND/OR <OLD.C1 condition>
are perfectly well formed.
SELECT statements without FROM clause are also perfectly well formed:
INSERT INTO .... SELECT NEW.C1 WHERE NEW.C1<>OLD.C1;
I've often read complaints on this forum about how SQLite's
non-deterministic trigger firing order is somehow "deliberately crazy".
The truth is the documentation is simply telling you that race conditions
are possible due to idiosyncratic query plans or lock acquisition order.
Yes, I too would prefer to have conditional statements better than SELECT
RAISE(IGNORE) in the trigger body. It would save a lot of duplicate code
in terms of triggers that differ only by a few characters in the body and
WHEN clause. But, one learns to make do.
On Fri, Jul 21, 2017 at 8:27 AM, Jean-Luc Hainaut <
jean-luc.hain...@unamur.be> wrote:
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
