Re: [sqlite] Trigger firing order
On 21/07/2017 19:00, Simon Slavin wrote: I’m minded to leave things as they are, with the order undefined. If you really want to trigger a number of different operations in a specific order, put those operations all in one trigger, one after another. Yes, possible now with the reminder of Peter: inserts can be conditional as well. Alternatively, instead of having ... have Operation A Trigger A1 on operation A performs operation B Trigger B1 on operation B performs operation C Trigger A3 on operation A performs operation D That way you’ll know that you’ll get either ABCD or ABDC, but either way C will be executed after B. Right, but not applicable in my special case: B is a global timestamp server that delivers unique timestamp Id's that are further used by several operations (e.g., closing the current state then creating a new current state). So trigger B1 does not know which operation will be performed next. Only triggers of type A1 know. I have a question. Would you expect to see depth-first or width-first recursion, and why ? I suppose you refer to the order cascading triggers must be fired, as in the architecture mentioned above. Am I right? Quite difficult question, to which I have no general answer (probably, there is none). In MY case (automatic management of transaction time temporal DB), I would say that a depth-first execution would be the most natural. If action A triggers actions B1 and B2, and action B1 triggers actions C11 and C12, I think that the final state of the data should be easier to understand if it results from sequence A.B1.C11.C12.B2, even if B2 modifies the result of A.B1.C11.C12. I can imagine that the result of A.B1.B2.C11.C12 could lead, in some cases, to unexpected final states. It seems that depth-first order is applied by Oracle (I don't know for others), though the protocol is fairly complicated by their "mutating table" concept, which often leads to convoluted programming. The documentation is unclear but it seems to suggest that the "follows/precedes" clauses apply to select the next sibling trigger after the child triggers have completed. But I may be wrong! Thanks for the suggestions and discussion JL ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger firing order
On 21/07/2017 18:13, petern wrote:
a programming pattern that is missing in SQLite trigger body: "if (new.C1
<> old.C1) then ". 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.
Quite right! Didn't think of this. Now, all invidual triggers have been
gathered into a single one.
Thanks
JL
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger firing order
On 21 Jul 2017, at 5:52pm, Simon Slavin wrote: > On 21 Jul 2017, at 4:27pm, Jean-Luc Hainaut > wrote: > >> 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. > > I’m minded to leave things as they are, with the order undefined. If you > really want to trigger a number of different operations in a specific order, > put those operations all in one trigger, one after another. Alternatively, instead of having Operation A Trigger A1 on operation A performs operation B Trigger A2 on operation A performs operation C Trigger A3 on operation A performs operation D have Operation A Trigger A1 on operation A performs operation B Trigger B1 on operation B performs operation C Trigger A3 on operation A performs operation D That way you’ll know that you’ll get either ABCD or ABDC, but either way C will be executed after B. > I have a question. Would you expect to see depth-first or width-first > recursion, and why ? Still interested in this. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger firing order
On 21 Jul 2017, at 4:27pm, Jean-Luc Hainaut wrote: > 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. I’m minded to leave things as they are, with the order undefined. If you really want to trigger a number of different operations in a specific order, put those operations all in one trigger, one after another. I have a question. Would you expect to see depth-first or width-first recursion, and why ? Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Trigger firing order
> a programming pattern that is missing in SQLite trigger body: "if (new.C1
<> old.C1) then ". 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 AND/OR
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 ". 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
[sqlite] Trigger firing order
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 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
