Geek Answers Handbook

Avoid Database Cursor in SQL Server

I have a puzzle (at least for me) that I hope, mainly because I'm not a master of the SQL universe yet. Basically, I have three tables:

Table A, Table B, and Table C.

Table C has an FK (Foriegn key) to table B, which has an FK for table A. (Each one is a lot for one)

I need to delete a record from table A and, of course, all its corresponding records from tables B and C. In the past, I used the cursor to do this, selecting all the records in table B and navigating through each one to delete all their corresponding records in Table C. Now it works - and it works fine, but I suspect / hope that there is a better way to achieve this effect without using cursors. So my question is, how can I do this without using a cursor, or can this be done?

(Please let me know if I was not understood - I will try to resolve the issue).

+5
source share
4 answers

Declare your FOREIGN KEYhowON DELETE CASCADE

+11
source

...

  • .

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

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

A, .

+8

(Cascading Deletes and Triggers) , , . , - 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)
+5
source

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

+2
source

More articles:


All Articles