Could you set the fk values on Table C to NULL when the original row is deleted? Then you could have a trigger on Table C that removes the row when both the a and b FKs are NULL.
Completely untested, but I’m thinking of something like this:
[code]CREATE TRIGGER Delete_A
AFTER DELETE ON A
BEGIN
UPDATE C SET a_id_fk = NULL
WHERE a_id_fk = OLD.a_id
END;
CREATE TRIGGER Delete_B
AFTER DELETE ON B
BEGIN
UPDATE C SET b_id_fk = NULL
WHERE b_id_fk = OLD.b_id
END;
CREATE TRIGGER Remove_c
AFTER UPDATE ON C
BEGIN
DELETE FROM C WHERE a_id_fk IS NULL AND b_id_fk IS NULL
END;[/code]
Thanks everyone for pointing me in the right direction. So far this seems to work.
I set up the foreign keys on Table C to cascade ON DELETE SET NULL. The I created a trigger on Table C:
CREATE TRIGGER after_update_table_c
AFTER UPDATE OF a_id, b_id ON Table_C
FOR EACH ROW
WHEN (NEW.a_id IS NULL AND NEW.b_id IS NULL)
BEGIN
DELETE FROM Table_C WHERE c_id = NEW.c_id;
END;