DELETE trigger

[TomBarrand]

When a DELETE statement is called on the FOB table I want to delete the FOB_Size records that relate to the FOB record that is trying to be deleted. How do I refer to the FOB.FOB_No that is trying to be deleted? My code is below

CREATE TRIGGER [DELETE_FOB_Size] ON [dbo].[FOB]
FOR DELETE
AS

DELETE FOB_Size
FROM FOB_Size, FOB
WHERE dbo.FOB_Size.FOB_No = dbo.FOB.FOB_No AND
FOB.FOB_No = @ThisFOBNo

 

[moorejp]

I'd recommend using the Inserted and Deleted temporary tables created during transactions. On a Delete action, all deleted records appear in the Deleted temp table while the Inserted temp table will be empty.


CREATE TRIGGER [DELETE_FOB_Size] ON [dbo].[FOB]
FOR DELETE
AS

DELETE FOB_Size
FROM FOB_Size INNER JOIN
Deleted ON FOB_Size.FOB_No = Deleted.FOB_No LEFT JOIN
Inserted ON Deleted.FOB_No = Inserted.FOB_No
WHERE Inserted.FOB_No IS NULL

Hope that helps.