sql>select * from run;
 
RUNNUMBER TESTNAME
--------- ----------
        1 DA1F
        1 DA1C
        1 DA1B
        2 ABCF
        2 ABCC
        2 ABCB
 
6 rows selected.
 
sql>create or replace package pkg_run
  2  is
  3    type array is table of run.runnumber%type index by binary_integer;
  4    v_array  array;
  5  
  6    procedure process;
  7  
  8  end;
  9  /
 
Package created.
 
sql>create or replace package body pkg_run
  2  is
  3  
  4    procedure process
  5    is
  6    begin
  7      for i in 1..v_array.count loop
  8        delete
  9          from run
 10         where runnumber = v_array(i)
 11           and testname like '%C';  -- or substr(testname, 4, 1) = 'C' if fixed width
 12      end loop;
 13    end;
 14  
 15  end;
 16  /
 
Package body created.
 
sql>  create or replace trigger run_bds
  2    before delete on run
  3  begin
  4    pkg_run.v_array.delete;
  5  end;
  6  /
 
Trigger created.
 
sql>create or replace trigger run_ad
  2    after delete on run
  3    for each row
  4    when (old.testname like '%F' or old.testname like '%B')
  5  begin
  6    pkg_run.v_array(pkg_run.v_array.count + 1) := :old.runnumber;
  7  end;
  8  /
 
Trigger created.
 
sql>create or replace trigger run_ads
  2    after delete on run
  3  begin
  4    pkg_run.process;
  5  end;
  6  /
 
Trigger created.
 
sql>delete from run where runnumber = 1 and testname = 'DA1F';
 
1 row deleted.
 
sql>select * from run where runnumber = 1;
 
RUNNUMBER TESTNAME
--------- ----------
        1 DA1B
 
1 row selected.
 
sql>delete from run where runnumber = 2 and testname = 'ABCB';
 
1 row deleted.
 
sql>select * from run where runnumber = 2;
 
RUNNUMBER TESTNAME
--------- ----------
        2 ABCF
 
1 row selected.
 
sql>select * from run;
 
RUNNUMBER TESTNAME
--------- ----------
        1 DA1B
        2 ABCF
 
2 rows selected.

when (old.testname like '%F')

delete  
  from run r1
 where runnumber = v_array(i) 
   and testname like '%C'
   and not exists (select null
                     from run r2
                    where r2.runnumber = r1.runnumber
                      and testname like '%B');

where r2.runnumber = r1.runnumber
  and <b>r2.</b>testname like '%B');