Oracle 11g: Compound triggers with an "on delete cascade" constraint

Question

In Oracle (11.2.0.1) I have 2 tables, let's say A and B with a referencial constraint between them ("on delete cascade"). A is a "parent" for B.

create table A (id number(9) not null primary key);
create table B (id number(9) not null primary key, parent_id not null references A(id) on delete cascade);

The child table also has a related compound trigger on deleting from it (The main purpose is to avoid mutation errors in a series of complex integrity checks).

create or replace trigger trg_b
for delete on b
compound trigger
  type b_table is table of b%rowtype;
  b_collection  b_table := b_table();

  before each row is
  begin
    b_collection.extend;
    b_collection(b_collection.last).id      := :old.id;
    b_collection(b_collection.last).parent_id := :old.parent_id;
  end before each row;

  after statement is
  begin
  for i in b_collection.first .. b_collection.last loop
    --logging into another table in an autonomous transaction
  end loop;
 b_collection.delete;
 end after statement; 

end trg_b;

One of the algorithms to work with these tables is following:

1. All rows with the specific parent_id will be deleted from B.
2. Additional check if no child rows are left.
3. Delete this specific id from the parent A.

In our production enviroment Step 3 generates an exception ORA-06502: PL/SQL: numeric or value error at string b_collection.first .. b_collection.last. It means a deletion from the parent table leads to firing the trigger on the child table even though there are no possible child rows on affect. It could make some sense since the collection doesn't exist when trigger is fired and the first and last collection indexes are NULL, but I just can not reproduce such behaviour.

In our dev enviroment I only get an ORA-06502 when I try to delete some non-existent id/parent_id from B. In its turns when I try to delete some existent (with no child rows) or non-existent id from A I get no errors - 0 rows deleted, 0 child rows affected. From logging I could tell the child related trigger isn't even fired in such cases. Why not?

Any ideas why the described behaviour may be so different? Did I miss something?

Answer 1

I figured it out. The Bug 8830338 - BEFORE and AFTER STATEMENT not executed in compound trigger for DELETE CASCADE (Doc ID 8830338.8) for 11.2.0.1 was involved into my fun with triggers. It's claimed to be fixed since the patchset 11.2.0.2, ever since such a compound trigger with an after statement should've been fired and ended up in a bad way.