Here is a new take on the classic mutating trigger issue in Oracle. Imagine a fictitious scenario where we have a requirement to do a cumulative sum on a column when an insert operation is performed on the table. Typically, we can write a trigger that looks up the table and uses the sum of the column to come up with a cumulative sum value for the row that is being inserted. The trouble with this approach is the moment we refer to the trigger table within the trigger itself, the chances are that the mutating trigger exception is waiting to happen.
So, how can we beat this problem? One way to do it is by a clever usage of INSTEAD OF trigger rather than a conventional trigger. Instead of allowing the insert to be performed on a table, if the insert were to be performed on a view of the table, that gives us the opening that we need to solve this problem!
Note: This is not the only mechanism to deal with mutating trigger issues. Well established mechanisms like using autonomous transaction, maintaining package level variables in statement level triggers etc. are also equally effective in solving this problem. Choosing the right mechanism would vary based on the exact use case.
Let us see how to solve this problem with an example:
--Creating a sample table to test the no mutation theory
create table no_mutation_t(running_num number, cum_sum number)
--Create a view on the table
create view no_mutation_v as select running_num from no_mutation_t
--Create a dummy sample data table that drives inserts on no_mutation_t table
create table no_mutation_sample_data_t(running_num number)
-- Insert rows in dummy sample data table
insert into no_mutation_sample_data_t values(10)
insert into no_mutation_sample_data_t values(20)
--Now, we are ready to write the instead of trigger.
create or replace trigger no_mutation_trg instead of insert on no_mutation_v
SELECT nvl(sum(running_num),0) into l_cum_sum
INSERT INTO no_mutation_t values(:NEW.running_num,:NEW.running_num + l_cum_sum);
-- Proof of the pudding lies in eating part. Test the trigger with an insert on view rather than the table
insert into no_mutation_v select * from no_mutation_sample_data_t
The insertion happens smoothly with no mutating trigger errors. A select on the no_mutation_t table produces the running_num and cumulative sum.
For completeness sake, you can actually write a trigger on the table with the same trigger code as above and test by inserting multiple rows into the table itself rather than the view. Doing so would result in a mutating trigger exception for the direct table insertion.