This content has been marked as final. Show 3 replies
What, exactly, are you trying to accomplish?
If this setup were to work, what would stop the infinite loop of triggers (an insert into A fires trigger_A which inserts into B which fires trigger_B which inserts into A which fires trigger_A...)?
Are you storing the same data in two different tables? If so, why? That violates basic normalization. Why didn't you choose a properly normalized design?
I tried to fixed it by using pragma autonomous_transaction but it is giving me deadlock error. Please help.Dom't use pragma autonomous_transaction in a trigger. The results can be detrimental to the data on your DB.
Remember a trigger can fire more than once for a particular event.
Welcome to the forum.
Triggers+autonomous transactions = recipe for disaster and the wrong way to go.
You should rethink what it is you're trying to accomplish.
Probably a COMMIT would get you past the deadlock error (if I recall correctly), but then your data can get corrupted, as you are messing with transactions.
Probably a procedural approach would be best.
Triggers have several disadvantages (slow-by-slow processing, performance, 'automagical' stuff happening), as you now are discovering.
Consult the Online Oracle Documentation regarding autonomous transactions and the deadlock error @
And for some more background: