4

I know this can be done with foreign keys but I cannot add them or something weird happens when I am inserting new records. There are a lot of stored procedures in this database and I don't know what they do since I know nothing about stored procedures. I was hoping someone could help me figure out a trigger that will delete a specific ProductID when I delete it from the Product table. It is also located in tables called CompanyLink, TargetLink, and CategoryLink.

As of right now, when I delete the ProductID from the Product table, I have to manually delete it from the other 3 tables it was inserted into.

Jamie
  • 1,579
  • 8
  • 34
  • 74

1 Answers1

7

You can do it through a trigger like this:

CREATE TRIGGER [dbo].[ProductDeleted]
ON [dbo].[Product]
AFTER DELETE
AS
BEGIN

   DELETE FROM CompanyLink WHERE ProductID = (SELECT TOP 1 ProductID FROM DELETED)
   DELETE FROM TargetLink WHERE ProductID = (SELECT TOP 1 ProductID FROM DELETED)

END

Obviously the syntax might not be perfect, but this is close to what you need.

James Johnson
  • 45,496
  • 8
  • 73
  • 110
  • 1
    I would question the presence of (SELECT top 1...) because SQL server triggers are statement level, and if a DELETE statement deletes multiple rows from Product, only the first will be deleted from child tables. – Ken Downs Aug 09 '11 at 17:10
  • That doesn't look as hard as I thought. Ken, what would you suggest using other than the SELECT top 1? – Jamie Aug 09 '11 at 17:41
  • So I have this: CREATE TRIGGER dbo.ProductDeleted ON dbo.Product AFTER DELETE AS BEGIN DELETE FROM CompanyLink WHERE ProductID = (SELECT ProductID FROM DELETED) DELETE FROM TargetLink WHERE ProductID = (SELECT ProductID FROM DELETED) DELETE FROM CategoryLink WHERE ProductID = (SELECT ProductID FROM DELETED) END – Jamie Aug 09 '11 at 18:09