Trigger delete on many-to-many middle table deletion

There are two tables A and B. As they have a many-to-many relation, there’s also table C.

A
------
id PK


B
------
id PK

C
------
id_A PK
id_B PK

Now, a row B only exists only exists when at least one row of A has a relation to it, and one B may contain a relation to two or more different rows of A.

My question is, how do i automatically delete a row from B if there isn’t any foreign to it in C? My initial though was to set a trigger, but i’m not to sure about this and i’d want a second opinion in how to proceed. Thank you.

Answer

First, one assumes that the data is initially set up correctly. That is, the only b records are the ones that meet your condition.

Then, the solution involves triggers on table c. When a row is deleted, it would check:

• Does id_b have any other rows in the table?
• If not, then delete the row.

This can actually be a bit tricky. In general, you don’t want to query the table being triggered. So, I might suggest an alternative approach:

• Add a counter on b.
• Add insert/update/delete triggers on c that increments or decrements the count in b.
• If the counter is 0 (or 1 before decrementing), then delete the row.

Gosh, you might find that the counter itself is sufficient, and there is no need to actually delete the row. You can get that effect if you use a view:

create view v_b as
    select b.*
    from b
    where ab_counter > 0;

You could also create a view on b and not have to deal with triggers at all:

create view v_b as
    select b.*
    from b
    where exists (select 1 from c where c.b_id = b.id);