Re: [sqlite] Recursive TRIGGERs: depth-first or width-first

[Pavel Ivanov]

> The same is true of FOREIGN KEY, by the way (I checked), but that's a bit 
> more obvious since breaking FOREIGN KEY will always result in a database the 
> programmer would consider corrupt.

You're not quite right. You're talking about immediate foreign keys.
There're deferred foreign keys too. See section 4.2 here:
http://www.sqlite.org/foreignkeys.html.

Pavel

On Wed, Nov 25, 2009 at 9:13 AM, Simon Slavin  wrote:
>
> On 25 Nov 2009, at 2:06pm, Pavel Ivanov wrote:
>
>>> I couldn't find the answer documented anywhere, so I will have to assume 
>>> that it may change in future versions.  Unless the requirement for depth 
>>> first is somewhere in the SQL specification.
>>
>> I believe it should be. Triggers should be executed before the
>> statement causing them to fire is considered successful. Otherwise all
>> triggers checking some constraints and prohibiting incorrect data (and
>> thus raise(...) function) are useless.
>
> Ahha.  That makes sense: it would have to RAISE an error and back out the 
> other transactions anyway, so it may as well do the TRIGGERs first.  Okay, on 
> that basis I can write my code assuming that it will always behave 
> depth-first.
>
> The same is true of FOREIGN KEY, by the way (I checked), but that's a bit 
> more obvious since breaking FOREIGN KEY will always result in a database the 
> programmer would consider corrupt.  I wrote some code in another DBMS once 
> that betrayed that it did some really nasty things when one FOREIGN KEY 
> triggered another one.
>
> Thanks for the help.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive TRIGGERs: depth-first or width-first

[Simon Slavin]

On 25 Nov 2009, at 2:06pm, Pavel Ivanov wrote:

>> I couldn't find the answer documented anywhere, so I will have to assume 
>> that it may change in future versions.  Unless the requirement for depth 
>> first is somewhere in the SQL specification.
> 
> I believe it should be. Triggers should be executed before the
> statement causing them to fire is considered successful. Otherwise all
> triggers checking some constraints and prohibiting incorrect data (and
> thus raise(...) function) are useless.

Ahha.  That makes sense: it would have to RAISE an error and back out the other 
transactions anyway, so it may as well do the TRIGGERs first.  Okay, on that 
basis I can write my code assuming that it will always behave depth-first.

The same is true of FOREIGN KEY, by the way (I checked), but that's a bit more 
obvious since breaking FOREIGN KEY will always result in a database the 
programmer would consider corrupt.  I wrote some code in another DBMS once that 
betrayed that it did some really nasty things when one FOREIGN KEY triggered 
another one.

Thanks for the help.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive TRIGGERs: depth-first or width-first

[Pavel Ivanov]

> I couldn't find the answer documented anywhere, so I will have to assume that 
> it may change in future versions.  Unless the requirement for depth first is 
> somewhere in the SQL specification.

I believe it should be. Triggers should be executed before the
statement causing them to fire is considered successful. Otherwise all
triggers checking some constraints and prohibiting incorrect data (and
thus raise(...) function) are useless.


Pavel

On Wed, Nov 25, 2009 at 8:59 AM, Simon Slavin  wrote:
>
> On 25 Nov 2009, at 1:38pm, Pavel Ivanov wrote:
>
>> Does this answers question?
>
> I think it does for the current version: depth first.  Thanks.
>
> I couldn't find the answer documented anywhere, so I will have to assume that 
> it may change in future versions.  Unless the requirement for depth first is 
> somewhere in the SQL specification.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive TRIGGERs: depth-first or width-first

[Simon Slavin]

On 25 Nov 2009, at 1:38pm, Pavel Ivanov wrote:

> Does this answers question?

I think it does for the current version: depth first.  Thanks.

I couldn't find the answer documented anywhere, so I will have to assume that 
it may change in future versions.  Unless the requirement for depth first is 
somewhere in the SQL specification.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Recursive TRIGGERs: depth-first or width-first

[Pavel Ivanov]

Does this answers question?

sqlite> create table log (t);
sqlite> create table t1 (a);
sqlite> create table t2 (a);
sqlite> create trigger tt1 after update on t1 begin
   ...> insert into t2 values (new.a);
   ...> insert into log values ("update of t1, a="||new.a);
   ...> end;
sqlite> create trigger ttt1 after insert on t1 begin
   ...> insert into log values ("insert into t1, a="||new.a);
   ...> end;
sqlite> create trigger ttt2 after insert on t2 begin
   ...> insert into log values ("insert into t2, a="||new.a);
   ...> end;
sqlite> insert into t1 values (1);
sqlite> insert into t1 values (2);
sqlite> insert into t1 values (3);
sqlite> insert into t1 values (4);
sqlite> insert into t1 values (5);
sqlite> update t1 set a = 6;
sqlite> select rowid, t from log;
1|insert into t1, a=1
2|insert into t1, a=2
3|insert into t1, a=3
4|insert into t1, a=4
5|insert into t1, a=5
6|insert into t2, a=6
7|update of t1, a=6
8|insert into t2, a=6
9|update of t1, a=6
10|insert into t2, a=6
11|update of t1, a=6
12|insert into t2, a=6
13|update of t1, a=6
14|insert into t2, a=6
15|update of t1, a=6


Pavel

On Tue, Nov 24, 2009 at 3:20 PM, Simon Slavin  wrote:
> This new implementation of recursive TRIGGERs.  Is it depth-first or 
> width-first ?
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users