避免SQL Server中的数据库游标

I have a bit of a puzzle (at least for me) which I am hoping is mostly because I am not yet an SQL master of the universe. Basically I have three tables:

我有一点想法(至少对我而言),我希望这主要是因为我还不是宇宙的SQL大师。基本上我有三张桌子:

Table A, Table B, and Table C.

表A,表B和表C.

Table C has a FK (Foriegn Key) to Table B, which has FK to Table A. (Each of these is many to one)

表C具有表B的FK(Foriegn Key),其具有表A中的FK。(每个都是多对一)

I need to remove an entry from Table A and of course all of it's corresponding entries from Tables B and C. In the past I've used a cursor to do this, selecting all the entries in Table B and cycling through each one to delete all their corresponding entries in Table C. Now this works - and has been working fine, but I suspect/hope there is a better way to achieve this effect without the use of cursors. So that's my question - how can I do this without using a cursor, or can it be done?

我需要从表A中删除一个条目,当然还有表B和C中所有相应的条目。过去我使用了一个游标来执行此操作,选择表B中的所有条目并循环访问每个条目以删除表C中所有相应的条目。现在这个工作 - 并且一直工作正常,但我怀疑/希望有一种更好的方法来实现这种效果而不使用游标。所以这是我的问题 - 如何在不使用光标的情况下完成此操作,还是可以完成?

(Please let me know if I haven't been clear - I'll try to fix up the question).

(如果我不清楚,请告诉我 - 我会尽力解决问题)。

4 个解决方案

#1

Declare your FOREIGN KEYs as ON DELETE CASCADE

将您的FOREIGN KEY声明为ON DELETE CASCADE

#2

You could do this a couple ways...

你可以用两种方式做到这一点......

You could just use cascading deletes on your foreign keys.

您可以在外键上使用级联删除。

CREATE TABLE TableB
    (FKColumn INT,
     CONSTRAINT MyFk FOREIGN KEY (FKColumn) 
         REFERENCES TableA(PKColumn) ON DELETE CASCADE)

You could use delete triggers on each table to delete the related records.

您可以在每个表上使用删除触发器来删除相关记录。

CREATE TRIGGER cascade_triggerA
    ON TableA 
    FOR DELETE
AS 
BEGIN

    DELETE TableB
    FROM   TableB JOIN DELETED ON TableB.FKColumn = DELETED.PKColumn

END

CREATE TRIGGER cascade_triggerB 
    ON TableB 
    FOR DELETE
AS 
BEGIN

    DELETE TableC
    FROM   TableC JOIN DELETED ON TableC.FKColumn = DELETED.PKColumn

END

If you're using MS SQL server, you could also use INSTEAD OF DELETE triggers. In this case, you'd create the trigger just on TableA - and in the trigger put all of the logic to delete the records from all 3 tables.

如果您正在使用MS SQL服务器,则还可以使用INSTEAD OF DELETE触发器。在这种情况下,您只需在TableA上创建触发器 - 并在触发器中放置所有逻辑以删除所有3个表中的记录。

In any of the above cases, you'd just delete the record from table A, and let the cascading and triggers take care of the rest.

在上述任何一种情况下,您只需删除表A中的记录,让级联和触发器处理其余的事情。

#3

The answers already given (Cascading Deletes and Triggers) are great, but you might work in an environment where these are not an option. If so, below is a purely SQL solution. The example is solely concerned with the DELETE syntax. In the real world you'd probably wrap this within a transaction and implement it as a stored procedure.

已经给出的答案(级联删除和触发器)很棒,但您可能在不能选择这些选项的环境中工作。如果是这样,下面是纯粹的SQL解决方案。该示例仅涉及DELETE语法。在现实世界中,您可能将其包装在事务中并将其实现为存储过程。

--
DECLARE @Param_PK_TableA   int
SET     @Param_PK_TableA   = 1500


-------------------------------
-- TABLE C --------------------
DELETE TableC

FROM TableC

     INNER JOIN TableB
             ON TableB.TableB_ID    = TableC.TableB_ID

     INNER JOIN TableA
             ON TableA.TableA_ID    = TableB.TableA_ID 

WHERE
    (TableA.TableA_ID = @Param_PK_TableA)



-------------------------------
-- TABLE B --------------------
DELETE TableB

FROM TableB

     INNER JOIN TableA
             ON TableA.TableA_ID    = TableB.TableA_ID

WHERE
    (TableA.TableA_ID = @Param_PK_TableA)



-------------------------------
-- TABLE A --------------------
DELETE TableA

WHERE
    (TableA.TableA_ID = @Param_PK_TableA)

#4

When you create the foreign key relationship for both tables you can specify ON DELETE CASCADE and it will take care of this for you when you delete a record in A.

为两个表创建外键关系时,可以指定ON DELETE CASCADE,当您在A中删除记录时,它将为您处理此问题。

#1