Hi,
I'm working on a db related task and struck with a trigger update.
I have 3 tables table t1, t2, t3. T1 is my master table. T2 is staging table and T3 is table at other schema.
Whenever there is a new entry on T1, it should be logged to T2 and T2 will update T3 based on various validation checks.
My current trigger is written as whenever there is any insert or update on T1, then make an entry to T2 and it updates T3.
However, when insert on T3 fails, the main insert on T1 is also rolled back. For me the transaction on T1 should always be successful(commit) irrespective of T3.
Hence, I thought to log the failed details in a staging table T2.
So that trigger on T1 will be inserting to T2 and Trigger on T2 will insert to T3. Even though if T3 insert fails transaction the transaction on T1 should be committed and completed.
T2 will be used to log whether the insert on T3 is success or failure.
I only have permission to proceed with triggers. Is there any way to succeed this with triggers?
I read about the autonomous_transaction but it doesn't seem to be working on this scenario.
can someone please help me. I'm using oracle 12c.