OK, here's a workaround a little out of left field :-)
First I'll recreate the problem with some simple tables
par = parent
chd = child
par_log = logging table
SQL>
SQL> create table par ( p int primary key );
Table created.
SQL> create table chd ( c int primary key, p int references par ( p ));
Table created.
SQL>
SQL> create table par_log
2 ( mod_type varchar2(1),
3 mod_seqno number,
4 mod_edit_date date,
5 transaction_id varchar2(50),
6 mod_action varchar2(1),
7 parent number);
Table created.
SQL>
SQL>
SQL> insert into par values (1);
1 row created.
SQL> insert into par values (2);
1 row created.
SQL> insert into par values (3);
1 row created.
SQL>
SQL> insert into chd
2 select rownum,1 from dual
3 connect by level <= 100;
100 rows created.
SQL>
SQL> insert into chd
2 select rownum+100,2 from dual
3 connect by level <= 100;
100 rows created.
SQL>
SQL> exec dbms_errlog.create_error_log('PAR')
PL/SQL procedure successfully completed.
SQL>
SQL> create or replace
2 trigger PAR_TRG
3 before delete on par
4 for each row
5 begin
6 insert into par_log
7 values ('N',1,sysdate,dbms_transaction.local_transaction_id,'D',:old.p);
8 end;
9 /
Trigger created.
SQL> delete from par
2 where p = 1
3 log errors reject limit unlimited;
delete from par
*
ERROR at line 1:
ORA-02292: integrity constraint (MCDONAC.SYS_C0029183) violated - child record found
SQL>
Now I'll drop the trigger and recreate it to do the same work but as a *compound* trigger
SQL> drop trigger par_trg;
Trigger dropped.
SQL> create or replace trigger par_trg
2 for insert or update or delete on par
3 compound trigger
4
5 before each row is
6 begin
7 insert into par_log
8 values ('N',1,sysdate,dbms_transaction.LOCAL_TRANSACTION_ID,'D',:old.p);
9 end before each row;
10
11 end;
12 /
Trigger created.
SQL>
SQL> delete from par
2 where p = 1
3 log errors reject limit unlimited;
0 rows deleted.
Clearly this shouldn't be a differentiating factor, so I'll be logging a bug but this should get you moving.
Also...to get this work done, here's an easy justification for you :-) Using a compound trigger you can improve the performance.
Your current trigger probably looks like
- get sysdate
- get a sequence number
- insert log header
- insert log detail
on a row by row basis.....ugh :-)
A compound trigger lets you do this:
create or replace trigger par_trg
for insert or update or delete on par
compound trigger
type list_of_header is table of header_table%rowtype
index by pls_integer;
type list_of_detail is table of detail_table%rowtype
index by pls_integer;
l_header list_of_header;
l_detail list_of_detail;
before statement is
begin
l_header.delete;
l_detail.delete;
end before statement;
before each row is
begin
l_header(l_header.count+1).co11 := ...;
l_header(l_header.count).co12 := ...;
l_header(l_header.count).co13 := ...;
l_detail(l_detail.count+1).col1 := ....;
l_detail(l_detail.count).col2 := ....;
l_detail(l_detail.count).col3 := ....;
end before each row;
after statement is
begin
forall i in 1 .. l_header.count
insert into header_table values l_header(i)
forall i in 1 .. l_detail.count
insert into detail_table values l_detail(i)
end after statement;
end;
/
and that code obviously does not need to be hand written - you can simply query the dictionary to auto-generate it. See examples in my git repos for auditing for this
https://github.com/connormcd/audit_utility