DBAs and PL-SQL experts - Is this possible ?

skyripper · 30 Oct 2017 at 12:38

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.

Code:

  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).

touch · 30 Oct 2017 at 13:15

Could you create a view instead of a table? Just use the data from table a with the required logic and reference that instead of table B.

skyripper · 30 Oct 2017 at 13:22

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.

Ergates · 31 Oct 2017 at 21:27

Post insert trigger (emphasis on post) - kick off a stored proc that does the copy/delete. Hopefully that would avoid the mutating table.

Obviously triggers are evil, but they're useful from time to time.

kkelly · 10 Nov 2017 at 13:33

Anonymous transaction or put the original table on another schema and build a view from it

Dolph · 12 Nov 2017 at 08:50

Does it need to be handled real time? If so, trigger as mentioned with lots of documentation so everyone understands exactly what it does and under what conditions.

If it doesn't, have a periodic run of a stored procedure to make the necessary changes.

I would question why you would need to do this though, it seems like it has the potential to be a bit of a bodge, the sort of thing that seems like a good idea now and causes hair to be torn out and much bad language in the future if someone has to debug it...

skyripper · 12 Nov 2017 at 10:50

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.

Dolph · 12 Nov 2017 at 17:02

Glad you've found a solution. The bodge to resolve a bodge problem is all too common sadly, but figured it was worth mentioning just to be clear

Ergates · 15 Nov 2017 at 22:33

A bodge on top of a bodge - well done, you've made a kludge. It won't be your last.

Ergates · 16 Nov 2017 at 11:39

Deep breaths man. That wasn't an insult or a slur, more of a "Welcome to the club". 90% of life is a compromise of sub-optimal solutions to problems you didn't create.

AHarvey · 17 Nov 2017 at 14:00

Ergates said:
Deep breaths man. That wasn't an insult or a slur, more of a "Welcome to the club". 90% of life is a compromise of sub-optimal solutions to problems you didn't create.

I can vouch for that