On Thu, 2007-01-18 at 11:21 -0800, mikpol wrote:
> Hi,
>
> I got a weid behavior while writing some triggers. It would be best to show
> it using an example. Basically, I have two tables A and B. When something is
> inserted into A, it is inserted into B with a trigger using the rowid of
> newly inserted row of A. However, if this id already exists in B, nothing
> should be inserted. Here's the SQL code:
>
> create table A (stuff text not null);
> create table B (id int not null unique, stuff text not null);
> create trigger trig after insert on A for each row begin
> insert or ignore into B values (new.rowid, new.stuff);
> end;
>
> Notice how the trigger does "or ignore", so if a row with that id was
> already inserted into B manually, inserting a row with that rowid into A
> should not change B (since id column of B has "unique" property). Now some
> table manipulations:
>
> insert into A values ('hello');
> select * from B;
> id stuff
> ---------- ----------
> 1 hello
>
> This worked as it should. It correctly inserted a row into B with rowid and
> stuff passed to A. Next I do:
>
> insert into B values (2, 'THIS SHOULD NOT CHANGE');
> insert into A (rowid,stuff) values (2, 'I WANT TO CHANGE THIS');
> select * from B;
> id stuff
> ---------- --------------------------------
> 1 hello
> 2 THIS SHOULD NOT CHANGE
>
> Here I inserted a row with id 2 into B before inserting into A with rowid 2.
> In this case I don't want the trigger to change B, and "on ignore" conflict
> strategy in the trigger should take care of that. This case works correctly
> since the value in B stayed the same. Now comes the weird case:
>
> insert into B values (3, 'THIS SHOULD ALSO NOT CHANGE');
> insert or replace into A (rowid,stuff) values (3, 'I CHANGED THIS');
> select * from B;
> id stuff
> --- --------------------------------
> 1 hello
> 2 THIS SHOULD NOT CHANGE
> 3 I CHANGED THIS
>
> Again, I inserted row with id 3 into B, expecting the trigger not to change
> it when row with rowid 3 is inserted into A. However, specifying "or
> replace" in the insert statement while inserting into A changes the behavior
> of the trigger, and now my value in B is changed. In other words, "or
> replace" in insert statement that fires the trigger has overridden the "or
> ignore" behavior in the trigger itself.
>
> Why does this happen? From my understanding, the trigger should just fire
> after insert is done into A, and should behave the same no matter what
> on-conflict strategy is used. My version of sqlite is 3.2.8.
Not so, as it turns out. It's tricky to find, but in the CREATE TRIGGER
documentation:
"An ON CONFLICT clause may be specified as part of an UPDATE or
INSERT trigger-step. However if an ON CONFLICT clause is specified
as part of the statement causing the trigger to fire, then this
conflict handling policy is used instead."
http://www.sqlite.org/lang_createtrigger.html
Dan.
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
