I'm working with a system that, in the most generous terms that I can come up with, has a horrendous data model. There is a set of tables that are "peers," in that they have a 1-1 relationship and the PK of table A is the PK in tables X1 to Xn. Table A is the "master" and tables X... are "auxiliary" (ORM inheritance). All these table may have related child tables. Table A must have a related row in 1 and only 1 of the auxiliary tables, and when a row is deleted from an auxiliary table the corresponding row must be deleted from table A, and vice-versa. I cannot enforce that the delete always begin with table A; it may be initiated at any of the peer tables. I've tried combinations of cascading deletes and initially deferred foreign keys which work when there is exactly one auxiliary table, but you can't put multiple FKs on a single column, so it doesn't work for the 1..n tables. I've tried putting a trigger on the auxiliary table that deletes the parent which in turn is related to the other tables with a cascading delete, but this fails with a data mutating error.

Since remodeling the tables (e.g. composition instead of inheritance) is, sadly, off the table, the "best" remaining alternative appears to be that all deletes are done by stored procedure. This has the problem that the "n" part of Xn is not static over time. As new peer tables are added someone has to remember to update the stored procedure, and my organization has demonstrated a deficiency in that area.

Statements 3 and 5 of the liveSql script have error trapping for the mutating data comment out. Without the error trapping the process fails on the errors. With the error trapping, the master row does not get deleted.

Any ideas?

create or replace view my_central_view as 
  select m.*, description_1, description_2
  from   my_central_table m
  left join my_auxiliary_table_1 t1
  on     pk = t1.auxiliary_key 
  left join my_auxiliary_table_2 t2
  on     pk = t2.auxiliary_key ;
  
create or replace trigger view_iiud 
instead of insert or update or delete on my_central_view
begin
  if deleting then
    delete my_central_table
    where  pk = :old.pk;
    
    delete my_auxiliary_table_1
    where  auxiliary_key = :old.pk;
    
    delete my_auxiliary_table_2
    where  auxiliary_key = :old.pk;
  end if;
end;
/

delete my_central_view;