Instead Of Delete多行触发器代码

Question

以下是我所拥有的表格的示例：

 Parent: Id  Type
  Row-1  1     a
  Row-2  2     a
  Row-3  3     b

 Child:  Id  Type  Col
  Row-1  1     a   ....
  Row-2  1     b   ....
  Row-3  2     a   ....

当我从Parent表中删除一行时，与Id & type匹配的对应行也必须从Child表中删除。

下面的INSTEAD OF DELETE触发器可用于从父表中删除单行，但在删除多行时将失败。有没有人能提出一个可行的解决方案。

CREATE TRIGGER [InsteadOFDelete] ON [dbo].[Parent] 
    INSTEAD OF DELETE 
AS 

BEGIN

    IF @@ROWCOUNT = 0 
        RETURN

    DECLARE @id Int , @type nvarchar(max)
    Select @id = Id,@type = Type  FROM deleted

    DELETE FROM Child WHERE Child.Id = @id AND Child.Type = @type;
    //Then delete from the parent table

END

Answer 1

您可以使用inner对deleted表执行此操作，如下所示：

delete C
from Child as C
    inner join deleted as D on D.ID = C.ID and D.Type = C.Type

delete P
from Parent as P
    inner join deleted as D on D.ID = P.ID and D.Type = P.Type