I have 3 tables like this:

• Table1: Id, Name
• Table2: Id, Name
• Table3: Id, EntityName (nvarchar), EntityId (int)

There are no foreign keys on Table3. I am inserting rows for Table1 and Table2 to Table3 like this:

When I delete a row from Table1 or Table2, how can I cascade it from Table3?

CodePudding user response：

You can create a delete trigger on each table to delete the matching rows from table3, for example:

create trigger Table1Delete on dbo.table1
for delete 
as
if @@RowCount = 0 return
set nocount on

delete from t3
from deleted d
join table3 t3 on t3.EntityName='Table1' and d.Id=t3.EntityId

And a similar one for Table2

CodePudding user response：

This is how you could write queries to "mimic" cascade delete:

delete from table2 t2
where exists(select 1 from table3
             where t2.id = entityId and EntityName = 'Table2')

delete from table1 t1
where exists(select 1 from table3
             where t1.id = entityId and EntityName = 'Table1')

If you have some condition upon which you delete from table3 you should also inlcude it in those queries.

UPDATE

To do it automatically, you need todefine foreign keys with cascade delete action. But only one foreign key can be defined on column, thus you'd need to have two columns - one for referencing table1 and second for referncing table2. Then on both you'd need define cascade delete.

With current design (one column to reference multiple tables) this isn't possible and you would need to work around (as suggested implement delete trigger on table).