i have two tables Table1 and Table2. Table1 have columns ID,stringIDs and Table2 Columns ID,data
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
'stringIDs' from table1 save them in another table with foreign key reference than apply trigger on new table to delete records from table 2
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.