I got a requirement to create a trigger on a table X and wanted to update the same table X in trigger body.
But this will return Mutating error, as same table is used.
Hence I used alternative approach.
CREATE OR REPLACE TRIGGER new_trigger AFTER INSERT ON X FOR EACH ROW
insert into dummy_table values(new.value1, new.value2....);
CREATE OR REPLACE TRIGGER another_trigger AFTER INSERT ON dummy_table FOR EACH ROW
insert into X values (new.value1, new.value2....);
This should work logically. But when I tested, it succeeds only once in 5 times. It will not complete step 1 itself.
Why is this discrepancy? Is it because I am using autonomous transaction?
What can be the possible workaround for this ?
Firstly, mutating is caused by accessing the same table on which is the trigger fired (or when multiple triggers are fired, all tables which are involved). The code you posted does not do that - so the problem is in another code/trigger which you did not post. Maybe you should analyze the whole error stack, not only its first line.
Anyway, even in the posted code, you have different problem. After INSERT to X, a trigger INSERTing to DUMMY_TABLE is fired. Which fires the second trigger which INSERTs to X. After that, a trigger INSERTing to DUMMY_TABLE is fired ... and you are in endless loop unless <condition> handles it. Which is impossible to tell from your post.
Reconsider, whether you want to do it in a database trigger and not in the application. Also think about your design and rather normalize it instead of making multiple workarounds to keep it "working" (whatever it is, as you did not post any details, I cannot judge it; but bad design often leads to requirements like these).
The only correct workaround is described in the AskTom links I posted previously - e.g. this: http://asktom.oracle.com/pls/asktom/...D:290416059674. Shortly, it consists of declaring additional collection variable(s) and creating three triggers: before statement for clearing them, before/after row for filling them and after statement for running conflicting statements.
Involve only necessary table - as you could see, spreading it to multiple tables or abusing autonomous transactions is the wrong (and blind) way.