How can trigger error be bypassed and allow main transaction to run

I have a trigger on a table which runs upon any DML and inserts to audit tables. However, upon a mismatch in data type between txn and audit data type, the trigger failed, and it rolled back the original txns/ simply it did not run throwing an error in the SQL developer.

As per my understanding, this is the default behavior of the Oracle database. Is there any way to bypass this error and allow the main txn to run?

I tried pragma autonomous in the trigger but had no luck.

Answer

The way I understood it, it is you to blame, not Oracle.

upon a mismatch in data type between txn and audit data type, the trigger failed

Basically, for illustration,

• txn table contains column named ORDER_ID whose datatype is VARCHAR2
• audit table contains column named ORDER_ID whose datatype is NUMBER

Transaction performs

insert into that_table (order_id) values ('A');

Trigger fires and tries to

insert into audit_table (order_id) values (:new.order_id);

which evaluates to

insert into audit_table (order_id) values ('A');

but hey! audit_table.order_id column's datatype is NUMBER, you can't put 'A' into it!

What to do? Fix it! so that audit table contains columns whose datatypes match txn table's. You'll need alter table to do that. Just beware - if column isn't empty, you won't be able to do it in a single step (but that's another problem).

---

As of the way you're auditing changes: yes, an autonomous transaction trigger is the way to do it, so that commit in it doesn't affect main transaction. But, autonomous transaction still follows rules as any other program unit; that fact won't (and doesn't, as you noticed) overcome errors you made. You could have used an exception handler, when others being the most mis-used one and ignore errors of such a type (datatype mismatch), but - that's most certainly a wrong way to "fix" things.