SQL Server for delete触发器使用过程删除其他表中的记录

Question

我有一个类似table1（table1_id, col1, some_ID）和另一个table2（table2_id, col1, table1_id）的表

我有一个程序

PROC deleteTable2(@id int)
AS
BEGIN 
    DELETE table2 
    WHERE table1_id = @id
END

我正试图为table1表创建一个删除触发器，所以当我试图从table1中删除时，该触发器将删除table2中的所有记录。

我写的触发器是这样的：

CREATE TRIGGER deleteTable1 on table1
FOR DELETE
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @id int = 0
    SELECT @id = del.table1_ID from deleted as del
    EXECUTE dbo.deleteTable2 @id

如果some_ID只有一条记录，它会起作用，但如果有更多记录，它就不起作用。例如：

delete table1 
where some_ID='some value'

此SQL查询将删除表1中的所有记录，并且只删除表2中的第一条记录。

这很有道理，所以我用CURSOR做了一个触发器，类似

CREATE TRIGGER deleteTable1 ON table1
FOR DELETE
AS
BEGIN
    DECLARE @id int = 0, @CURSOR cursor
    SET @CURSOR = CURSOR scroll FOR 
       SELECT deleted.table1_id 
       FROM deleted
    OPEN @CURSOR
    FETCH NEXT FROM @CURSOR into @id
    WHILE @@FETCH_STATUS = 0
    BEGIN
          EXECUTE dbo.deleteTable2 @id
          FETCH NEXT FROM @CURSOR into @id
    END
    CLOSE @CURSOR
END

但它根本不起作用。。。也许我只是错过了什么，或者我没有得到一些细微的差别。非常感谢。

更新

事实上，这两个表在一个数据库中，但我有第三个表table3 (table3_id, col1, table2_id)。这个table3在其他数据库中，它们通过链接服务器连接。因此，当我在尝试时调用存储过程时，这个存储过程会调用存储过程（带光标）来删除table3中的记录。这正是我尝试使用存储过程删除表2中记录的原因。

Answer 1

避免使用所有这些不必要的游标和存储过程。只需遵循基于集合的方法，并在触发器内执行以下操作。

CREATE TRIGGER deleteTable1 on table1
FOR DELETE
AS
BEGIN
SET NOCOUNT ON
Delete FROM Table2 
WHERE Exists (SELECT 1 
              FROM deleted del
              WHERE del.table1_ID = table2.Table1_ID)             
END

更新

由于您已经提到，您也希望从第三个表中删除记录，该表是链接服务器上的某个位置。

我建议您使用相同的方法，只需在链接服务器上第三个表的过程中添加另一条delete语句。

CREATE TRIGGER deleteTable1 on table1
FOR DELETE
AS
BEGIN
SET NOCOUNT ON
Delete FROM Table2 
WHERE Exists (SELECT 1 
              FROM deleted del
              WHERE del.table1_ID = table2.Table1_ID)             
DELETE FROM t3
FROM [LinkedServerName].[DBName].[SchemaName].Table3 t3 
WHERE Exists (SELECT 1 
              FROM deleted del
              WHERE del.table1_ID = t3.Table1_ID)             

END

Answer 2

CREATE TRIGGER deleteTable1 on table1
FOR DELETE
AS
BEGIN
    SET NOCOUNT ON
    DELETE FROM table2 WHERE table1_id IN (SELECT table1_id FROM deleted)
END