Mutating Tables in Oracle with Examples
In this article, I am going to discuss Mutating Tables in Oracle with Examples. Please read our previous article where we discussed Compound Triggers in Oracle with Examples. The compound triggers are used to solve multiple issues. One of the biggest issues that compound triggers solve is the mutating table.
What are Mutating Tables in Oracle?
Many developers do a mistake called Mutating Triggers. Let us understand the mutating table with an example. Let us go ahead and create a table customer by executing the below create table statement. The table customers contain two columns cust_id and cust_name.
Create table customers ( cust_id number, Cust_name varchar2(100) );
The customers table is created. Now, we will create a trigger on this table to populate cust_id. Please run the below SQL Query.
select max(cust_id) from customers;
Here, you can see the result is null because the table is empty. To solve this issue, we will be using nvl function as shown in the below query.
select nvl(max(cust_id),0) from customers;
Now, you can see the result is 0. So, we will be using nvl, and cust_id to populate the cust_id using a trigger. Let us go ahead and create a trigger by executing the below SQL statement.
create or replace trigger customers_seq before insert on customers for each row declare --use declare when you want define variables v_max_id number; begin select nvl(max(cust_id),0) +1 into v_max_id from customers; :new.cust_id:= v_max_id; end;
So, the code for the trigger is above. You can see the trigger name is customers and the event is before insert on the table customers. We have declared a variable v_max_id. You can declare any variables by mentioning declare in the middle of the triggers by using the following syntax.
We will be storing the maximum value of the cust_id from the table customers. So, the logic of the trigger is as follows.
select nvl(max(cust_id),0) +1
So, from the above statement, you can see the maximum value of the cust_id + 1 is stored in the v_max_id and then the v_max_id value is again stored in the cust_id as a new value. Let’s try to compile the trigger and see how it works.
The trigger is compiled successfully. Now, let us try to insert a record into the customers table by executing the below INSERT SQL Statement.
insert into customers (cust_name) values(‘abc’);
So, a new row is inserted into the table customers. Let’s see if the trigger is executed or not. We haven’t mentioned any value for cust_id. So, the value in the table should be 1 as we are adding +1 to the cust_id. Let us go ahead and check the table customers by executing the below SELECT Statement.
select * from customers;
You can see the cust_id is stored as 1. So, our trigger is executed successfully. Let us try to insert another record by executing the below insert statement and see if the cust_id value will be incremented to 2 or not.
insert into customers(cust_name) values (‘def’);
So, we have inserted another row. Let us see the customers table whether the cust_id is incremented or not by executing the below select statement.
select * from customers;
So, from the output, we can determine that the trigger is executed. The cust_id value is incremented to 2. So, there are the two records that we have inserted into the table customers. Let us try to insert multiple records in the customers table by executing the below SQL Statement.
Insert into customers (cust_name) Select first_name From employees;
Let us check the list of employees with first_name by executing the below query.
select first_name from employees;
We can see there are multiple names. You can see the first row is Ellen. If we try to insert Ellen into the customers manually it works fine. But if we try to insert into the table using the employees, we will get an error because the value Ellen is already present in the customers, and the trigger reads the previous value. This is mentioned as a mutating table. So, let us try to insert the record and see the error by executing the following statement.
Insert into customers (cust_name) Select first_name From employees;
We are getting an error with the error number as ORA-04091 describing table HR.CUSTOMERS is mutating, trigger/function many not see it.
ORA-04088: error duing execution of trigger ‘HR.CUSTOMER_SEQ’.
So, whenever a table is modified by an insert or update, or delete statement, and, at the same time, that needs to be read by trigger mutating table occurs. So, when we try to insert using the name Ellen, the record is already present, and trying to insert using the DLL operation while the trigger reading the table and identifying the record is already present. This is represented as Data Inconsistency.
The description for this issue is “the trigger code query same record inserted”. How we can solve this problem?
We can solve these types of issues using the compound trigger. Before beginning with the compound trigger, let us try to take an example from the table employees and use those records for this compound trigger and solve the mutating table issue.
Query: Select * from employees where job_id=’IT_PROG’ order by salary;
We have selected the employees with job_id as “IT_PROG”. The minimum salary of these employees is 4200 and the maximum salary is 9000.
So, we are going to create a trigger that prevents inserting/updating any employees with job_id=’IT_PROG’ and the salary out of the ranges of IT_PROG. So, any new employees who are being inserted should be in the same range i.e, 4200-9000. Any employee that needs to be updated should be in the same range as well. Below is the entire trigger statement. Let’s try to learn about this trigger statement.
create or replace trigger IT_PROG_range before insert OR UPDATE on employees for each row when (new.job_id='IT_PROG') declare v_min_IT_PROG number; v_max_IT_PROG number; begin select min(salary), max(salary) into v_min_IT_PROG,v_max_IT_PROG from employees where job_id=:new.job_id; if :new.salary not between v_min_IT_PROG and v_max_IT_PROG then raise_application_error (-20300,'invalid range'); end if; end;
We have created a trigger with the name IT_PROG_range where the event is insert or update on the table employees. This trigger is specified for each row.
We have mentioned as “when (new.job_id=’IT_PROG’). This is mentioned because the trigger will execute only if the job_id is IT_PROG. We have declared two variables v_min_IT_PROG & v_max_IT_PROG. V_min_IT_PROG is used to store the minimum value of the salary and v_max_IT_PROG is used to store the maximum value of the salary. Now, let us try to learn the exact logic inside the trigger. Please have a look at the following query.
begin select min(salary), max(salary) into v_min_IT_PROG,v_max_IT_PROG from employees where job_id=:new.job_id; if :new.salary not between v_min_IT_PROG and v_max_IT_PROG then raise_application_error (-20300,'invalid range'); end if; end;
Here, we are going to store the minimum salary i.e. 4200 into the value v_min_IT_PROG and the maximum value i.e. 9000 to v_max_IT_PROG. So if we insert any new record to the table employees with IT_PROG as the job_id then the salary will be :new.salary and the value will be compared between v_min_IT_PROG and v_max_IT_PROG. If the value :new.salary is not between these two values then we will be raising an application error (-20300, ‘invalid range’). Let us try to compile this trigger.
So, the trigger is compiled successfully. Let’s try to test the trigger. This trigger is reading the same table and inserts the record into the same table. Generally, we might be facing a mutating issue but, here we have declared two variables and assigned the values min(salary) and max(Salary) from the table, so the compiler doesn’t read the table while inserting the record. Let us try to insert a record and see how it works by executing the following statement.
insert into employees (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID, SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID ) VALUES (900,'JAY','JAY','DOTNET@GMAIL.COM',null,sysdate,'IT_PROG', 1700,0,null,90 );
We are inserting a record with first_name as jay and last_name as jay with email as firstname.lastname@example.org and the salary is 1700 which is not in the range of 4200 and 9000. Let’s try to run this insert statement. We should get an error saying “out of range”.
We got the same error and the same error number that we have mentioned in the logic of the trigger. We got the oracle error number as 20300. Now, let us try with an updated statement as follows.
Update employees Set salary=6000 Where employee_id=107;
We got the table as a mutating error. This is because we are updating the same record that the select statement is reading. These two tasks are working parallelly and the oracle throws table is a mutating error. We can fix this error by using the compound trigger. Let’s try to create a compound trigger by executing the below SQL Statement.
create or replace trigger IT_PROG_range for insert OR UPDATE on employees when (new.job_id='IT_PROG') compound trigger v_min_IT_PROG number; v_max_IT_PROG number; before statement is begin select min(salary), max(salary) into v_min_IT_PROG,v_max_IT_PROG from employees where job_id='IT_PROG'; end before statement; before each row is begin if :new.salary not between v_min_IT_PROG and v_max_IT_PROG then raise_application_error (-20300,'invalid range'); end if; end before each row; end;
From the above trigger statement, we can see the trigger name is IT_PROG_range for events both insert or update on the table employees. As this is the compound trigger we have mentioned the compound trigger. We have declared two variables v_min_IT_PROG and v_max_IT_PROG. Now, let us try to learn about the logic of the compound trigger. We are using the before statement trigger and before each row trigger.
The before statement trigger will store the minimum value and the maximum value of the salary to the variables v_min_IT_PROG and v_max_IT_PROG.
The before each row trigger will check if the new salary value (:new.salary) is between the range of v_min_IT_PROG and v_max_IT_PROG values.
When we do any update or insert, first the before statement will be executed and stores the max and min values into variables. These variables will be used later in before each row trigger. So, it will separate the transactions and the mutating of the table does not appear. Now, let us try to compile this trigger.
So, the trigger is compiled successfully. Let’s try to test this trigger. Let us try with the update statement as follows.
Update employees Set salary=200 Where employee_id=107;
In the next article, I am going to discuss Compound Triggers and Arrays to Solve Mutating in Oracle with Examples. Here, in this article, I try to explain Mutating Tables in Oracle with Examples. I hope you enjoy this Mutating Tables in Oracle with Examples article.