TRIGGER AUTONOMOS TRANSACTION fails each time when trigger needs to run with different ORA errors-CodePudding

I try to create a custom table, which is created properly. The last 2 fields are matching the type from Orders table. The trigger's idea is when a certain date field is changed to INSERT this into the new table. The trigger is created in Oracle correctly, but once the field in question changes I get ORA errors (below) and cannot find out why they appear.

ORA-06519: active autonomous transaction detected and rolled back

ORA-06512: at "shc.trigger_table_1", line 20

ORA-04088: error during execution of trigger 'shc.trigger_table_1'

I tried changing the datatypes, tried introducing an EXCEPTION, tried removing the :old.info23 check.

Here is the table I am creating:

CREATE TABLE table1(
id number generated by default as identity,
table_name varchar2(20),
field_changed varchar2(20),
old_value date,
new_value date,
changed_by varchar(20),
date_of_change date,
orderRef varchar(50),
Ref varchar2(256));

Here is the problematic trigger:

CREATE OR REPLACE TRIGGER trigger_table_1
AFTER UPDATE ON consignment
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DECLARE
my_AEXTERNAUFTRAGSNR varchar2(50);
my_AUFTRAGSREFERENZ2 varchar2(256);
BEGIN
IF :OLD.ANKUNFTBELDATUMVON <> :NEW.ANKUNFTBELDATUMVON AND :old.info23='I' THEN
Select AEXTERNAUFTRAGSNR, AUFTRAGSREFERENZ2
Into my_AEXTERNAUFTRAGSNR, my_AUFTRAGSREFERENZ2
From orders
Where orders.nr = :old.ordernr;
insert into table1(table_name, field_changed, old_value, new_value, changed_by, date_of_change, orderRef, Ref)
values ('consignment', 'ANKUNFTBELDATUMVON', :OLD.ANKUNFTBELDATUMVON, :NEW.ANKUNFTBELDATUMVON, sys_context('userenv','OS_USER'), SYSDATE,  my_AEXTERNAUFTRAGSNR,  my_AUFTRAGSREFERENZ2);
END IF;
END;
END;

CodePudding user response：

The immediate problem is that you don't commit your autonomous transaction. If you change it to commit at the end:

...
END IF;
END;

commit;

END;
/

then it will work - fiddle. (You don't need the nested block, but it doesn't stop it working... and you could do insert ... select ... to avoid needing any local variables...)

But as you can see from the db<>fiddle result, if the update on consignment is rolled back then the insert into table1 is retained, since that was committed independently.

If you remove the PRAGMA AUTONOMOUS_TRANSACTION; instead then that won't happen. Then you won't need to, and indeed can't, commit within the trigger. You would just need:

CREATE OR REPLACE TRIGGER trigger_table_1
AFTER UPDATE ON consignment
FOR EACH ROW
DECLARE
  my_AEXTERNAUFTRAGSNR varchar2(50);
  my_AUFTRAGSREFERENZ2 varchar2(256);
BEGIN
  IF :OLD.ANKUNFTBELDATUMVON <> :NEW.ANKUNFTBELDATUMVON AND :old.info23='I' THEN
    Select AEXTERNAUFTRAGSNR, AUFTRAGSREFERENZ2
    Into my_AEXTERNAUFTRAGSNR, my_AUFTRAGSREFERENZ2
    From orders
    Where orders.nr = :old.ordernr;

    insert into table1(table_name, field_changed, old_value, new_value, changed_by, date_of_change, orderRef, Ref)
    values ('consignment', 'ANKUNFTBELDATUMVON', :OLD.ANKUNFTBELDATUMVON, :NEW.ANKUNFTBELDATUMVON, sys_context('userenv','OS_USER'), SYSDATE,  my_AEXTERNAUFTRAGSNR,  my_AUFTRAGSREFERENZ2);
  END IF;
END;
/

or with insert ... select ...:

CREATE OR REPLACE TRIGGER trigger_table_1
AFTER UPDATE ON consignment
FOR EACH ROW
BEGIN
  IF :OLD.ANKUNFTBELDATUMVON <> :NEW.ANKUNFTBELDATUMVON AND :old.info23='I' THEN
    insert into table1(table_name, field_changed,
      old_value, new_value,
      changed_by, date_of_change,
      orderRef, Ref)
    select 'consignment', 'ANKUNFTBELDATUMVON',
      :OLD.ANKUNFTBELDATUMVON, :NEW.ANKUNFTBELDATUMVON,
      sys_context('userenv','OS_USER'), SYSDATE, 
      o.AEXTERNAUFTRAGSNR,  o.AUFTRAGSREFERENZ2
    from orders o
    where o.nr = :old.ordernr;
  END IF;
END;
/

fiddle

Incidentally, if you only want the trigger to fire if a specific column (or columns) was included in an update statement then you could do:

AFTER UPDATE OF ANKUNFTBELDATUMVON ON consignment

but you would still need to check it had actually changed. Your current check doesn't cover ANKUNFTBELDATUMVON being update to or from null, which is only potentially an issue if the column is nullable.