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] -----------------------------------------------------------------------------