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.

Any help would be greatly appreciated.


-- 
View this message in context: 
http://www.nabble.com/Incorrect-trigger-behavior--tf3036078.html#a8437145
Sent from the SQLite mailing list archive at Nabble.com.


-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------

Reply via email to