Get Identity of Deleted Record 1

[skispray]

I'm trying to set an SQL trigger so that when I delete a record from one table it deletes the records in another table with the same ID as the record just deleted.

I thought this would work but it doesn't:
DELETE FROM Table2 WHERE ID=@@IDENTITY

Can anyone help? Thanks!

 

[Veep]

Here's an example where I delete the records from 2 related tables when a record is deleted from the orders table.
CREATE trigger dbo.tr_Delete_Record_From_OrderDetail_Shipping
on
dbo.tbl_Orders
For Delete
AS

Declare @OrderID int
select @OrderID = OrderID from Deleted
DELETE tbl_OrderDetail WHERE OrderID=@OrderID
DELETE tbl_Shipping WHERE OrderID=@OrderID

 

[skispray]

Thank you much, it worked perfect!

 

[swampBoogie]

That code is unnecessary complicated and will not work correctly in some cases.

All you need is

DELETE from tbl_Shipping WHERE OrderID in (select OrderID from deleted)

 

[SQLSister]

swamp boogie is right, the other code only works if you delete only one record at a time, a very dangerous assumption in a trigger.

 

[sparkme]

But Swampboogie code has to be inside the Trigger because its using the Deleted table.