CODEWITHSUNDEEP
sqlite
Dan
Dan

Reputation: 13170

Row deleted when it shouldn't be

If I execute the following script in sqlite 3.8.8.3

PRAGMA foreign_keys=on;

create table A (
k integer primary key not null
);

create table B (
value text primary key,
k integer references A(k) on delete cascade
);

create table C (
k integer primary key,
value text
);

create trigger C_A before insert on C
begin
insert or ignore into A values(NEW.k);
end;

insert into A values (5);
select * from A;

insert into B values ('asdf', 5);
select * from B;

insert or ignore into A values(5); --just calling this works right
select * from B;

insert or ignore into C values (5, 'qwer'); --this works too
select * from B;  

insert or replace into C values (5, 'qwer'); --this doesn't work
select * from B; --doesn't print anything

I get the output

5
asdf|5
asdf|5
asdf|5

when I'm expecting the output

5
asdf|5
asdf|5
asdf|5
asdf|5

It seems like the "or replace" gets propagated into the trigger, replacing the row in A, and deleting the row in B.

Is this expected behavior? Am I misunderstanding what this should do?

Upvotes: 0

Views: 19

Answers (1)

CL.
CL.

Reputation: 180192

This is documented:

An ON CONFLICT clause may be specified as part of an UPDATE or INSERT action within the body of the trigger. However if an ON CONFLICT clause is specified as part of the statement causing the trigger to fire, then conflict handling policy of the outer statement is used instead.

Upvotes: 2

Related Questions