Avoid Database Cursor in SQL Server

Question

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

Answer 1

Declare your FOREIGN KEYs as ON DELETE CASCADE

Answer 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.

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.