Mutating trigger issue in oracle
I am facing mutating trigger error,
I will describe the issue here I am using tableA and tableB.
TableA holds a column called empChecked which can hold 2 values '-', '+'. TableB holds a column called mgrChecked which can hold 2 values '-', '+'.
The current requirement is both fields in empChecked and mgrchecked must be in sync. i mean if empChecked is updated to '+' then mgr checked in tableB must be updated to '+' and vice versa. Updation is possible from front end for both fields.
I have created trigger on both the tables. but i am facing ora-04091 error.
Please suggest me any approach to achieve this?
The mutating table error is a code smell. It almost always points to a bad data model, usually insufficient normalisation.
Certainly a bad data model is in evidence here. You have a column on a table with two settings. That's fine. Now you want to add the same column to a second table and keep the two in sync. This new column is completely pointless. There is no information available in that new column than you cannot get from querying the first table.
And that's what ORA-04091 is telling you. You can spend an awful lot of time building a workaround but that would all be wasted effort.