0

Below are the tables of relevance:

Table_B
B_ID(PK)  Name  A_Id
1         A     1
2         B     1

Table_C
C_ID   B_Id(FK)
1      1
2      1

Now I want to delete rows from Table_B and from its child table Table_C. Below is the query I am firing:

delete from Table_B where A_Id = 1

I want to delete all rows from its child too. Is it possible with a Trigger? I have made a Trigger on Table_B, but it deletes only one row; so I have to run a loop.

Thanks.

5

I don't see the need to use a trigger. You could use two DELETEs:

DELETE C
FROM Table_C C
INNER JOIN Table_B B
    ON C.B_ID = B.B_ID
WHERE B.A_ID = 1;

DELETE FROM Table_B 
WHERE A_Id = 1;

If you want to use a trigger, you can use this code inside:

DELETE C
FROM Table_C C
INNER JOIN DELETED B
    ON C.B_ID = B.B_ID;

Justa as a note, remember that if you have a foreign key in that table, you could configure it ON DELETE CASCADE (but be careful with your deletes)

  • Well, I tried trigger. – DhavalR Jun 14 '17 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.