CODEWITHSUNDEEP
sqlsql-servert-sqlcursors
Streklin
Streklin

Reputation: 849

Avoid Database Cursor in 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:

Table A, Table B, and Table C.

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

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?

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

Upvotes: 5

Views: 721

Answers (4)

Scott Ivey
Scott Ivey

Reputation: 41558

You could do this a couple ways...

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.

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.

Upvotes: 8

Matthew J Sullivan
Matthew J Sullivan

Reputation: 51

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.

--
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)

Upvotes: 5

Nick Larsen
Nick Larsen

Reputation: 18877

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.

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425331

Declare your FOREIGN KEYs as ON DELETE CASCADE

Upvotes: 11

Related Questions