SQL Server触发器从多个表中删除一条记录 [英] SQL Server Trigger to DELETE one record from multiple tables

问题描述

我知道可以使用外键来完成此操作，但是我无法添加外键，或者在插入新记录时发生奇怪的事情。这个数据库中有很多存储过程，我不知道它们的作用，因为我对存储过程一无所知。我希望有人可以帮我找出一个触发器，当我从Product表中删除特定的ProductID时，该触发器将删除它。它还位于称为CompanyLink，TargetLink和CategoryLink的表中。

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.

截至目前，当我从Product表中删除ProductID时，必须从插入的其他3个表中手动删除它。

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.

推荐答案

您可以通过以下触发器来做到这一点：

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.

这篇关于SQL Server触发器从多个表中删除一条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！