DBAs and PL-SQL experts - Is this possible ?

[skyripper]

We have an application. It writes to a table in an oracle database.
We can't modify the application or the SQL being used by the application.
We'd like to have it instead write to a different table if a particular condition is true.

  When inserting into Table A
     Insert into Table B instead
     Delete [Or don't commit] the row from Table A
  End

I've tried writing this using a trigger, and seem to have come unstuck with a "Mutating table" error.
Is there a better / different way to achieve this?

Not sure if we could use SQL rewritting either (we're on Oracle 12c).

 

[skyripper]

That's our alternative if we can't stop the data getting into the table in the first place but means we'd need to do loads of changes elsewhere.

 

[skyripper]

You're right the idea is a bodge. Only considered it as it's to sort out another bodge that's been found in our application that would take a lot of developer effort to undo and had been in place for years.

In the end I went with a create two Views route, and have made changes to the various reports using the original table. Does mean we have a ongoing maintenance task, but it's manageable.