SQLite In Clause not working in trigger

ado.net c# dapper sqlite

Question

i have two tables Table1 and Table2. Table1 have columns ID,stringIDs and Table2 Columns ID,data

There are two tables in picture

i have created a trigger to delete rows based on table1. it doesn't works if comma Seperated stringIDs are more than one. it works if stringIDs is only single value

create trigger tgTriggerName after delete
on Table1
begin
delete from Table2 where ID in (old.stringIDs);
end

Accepted Answer

'stringIDs' from table1 save them in another table with foreign key reference than apply trigger on new table to delete records from table 2


Popular Answer

Gordon is right, this table structure is really probably not what you want. But if for some reason you must do it this way, this query might accomplish what you want:

delete from Table2 
where ID = old.stringIDs                   -- ID matches exactly
  or old.stringIDs like ID + ',%'          -- Or ID is at beginning of list
  or old.stringIDs like '%,' + ID          -- Or ID is at end of list
  or old.stringIDs like '%,' + ID + ',%'   -- Or ID is in middle of list

But that's a mess. Don't do it. Instead remove the stringIDs column from Table1, and add a column to Table2 called Table1ID to indicate which Table1 ID this Table2 record belongs to. So Table2 would look like this

ID     Table1ID     Data
1      1            some data
2      1            some data
3      2            some data
4      2            some data
5      2            some data
...

Then your trigger query can simply be:

delete from Table2
where Table1ID = old.ID

Even more clean would be to skip the trigger completely and do a Foreign Key Contstraint with cascading delete. But I have a feeling that's a lesson for another day.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why