Trigger for deleting row on the basis of key in another table

(i) i have more then two table with a common field ID. Now is there any way to write a trigger so that whenever a row is deleted fron one table,the all rows in all other tables having same ID should be deleted.


(ii) another requirement is to write a trigger so that all rows in the same table having same ID are deleted where any row is deleted.

Although i have solved these problems through code in vb.net but i wants to use Stored procedures or triggers.

Hi Rinku!

You can create following trigger. I have some assumptions in my example.

Case [1] Other table have the same field ( Assumed as ID here). I assume Table2 and Table3 have the same ID column -
----------------------------------------------------------------------------------
Create trigger tr_001 on Table1
For Delete
as
delete from table2 where id in (Select id from deleted )
delete from table3 where id in (Select id form deleted )
go
----------------------------------------------------------------------------------

Case [2] Delete all rows when a row with same ID is deleted.

----------------------------------------------------------------------------------
CREATE trigger tr_002 on Table1
For delete
as
delete from Table1 where id in (select id from deleted )
go
----------------------------------------------------------------------------------
All the currently deleted values are stored in 'deleted' Table, that is used here. Inserted and Updated are also the tables that are used in various cases.

Reply soon ..

- Som Dutt