problem with mutating tables

Question

I want to create trigger which will be fired after insert into some table, let`s say user. In this trigger I want to select some records from table user, besides this one I insert to table, but then I receive error about mutating table. Is it possible to get records from this table?

Answer 1

Make the trigger run in an autonomous transaction (separate transaction)

create or replace trigger xxx
before .... on .....

declare
.....

pragma autonomous transaction;
begin
.....your code.....
end;

Regards
K

Answer 2

The autonomous transaction method is a workaround for this but doesn't address some other fundamental issues.

The major problem with this method is that when multiple modifications occur simultaneously to a table then the triggers that fire do not see the results of the other modifications. Therefore the results that you get from the trigger code can be incorrect.

For example, if you had a trigger on a table of "customer orders" that is intended to maintain a "customer total orders" column in the customer table, then two orders being placed at the same time cannot see each other. they each try to update the "customer total orders" with a total that excludes the other order.

For this and other reasons it is often considered bad practice to use triggers for business logic or to maintain other table data.

Answer 3

To paraphrase Tom Kyke:

when I hit a mutating table error, I've got a serious fatal flaw in my logic.

This is a quite complex problem. There are multiple solutions to it, but the best is to change your logic so that you don't run into such errors.

A possible solution can be found here. There are some more discussions about this on Ask Tom, search for them and read them. You'll gain some very valuable knowledge.