Compound Trigger and Array to Solve Mutating in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

Compound Trigger and Array to Solve Mutating in Oracle

In this article, I am going to discuss Compound Triggers and Arrays to Solve Mutating in Oracle with Examples. Please read our previous article where we discussed Mutating Tables in Oracle with Examples.

Compound Trigger and Array to Solve Mutating in Oracle

In the previous article, we have learnt about the mutating table & learnt how to solve the mutating table using the compound trigger. We have created a compound trigger to prevent inserting or updating the table employees in between the range of 4200 to 9000. Now, we will try to explore more about mutating tables and more complex examples. We will try to solve mutating tables using arrays and compound triggers.

Drop the Previous Trigger.

As we have created a trigger on table employees, let us drop the trigger IT_PROG_range by executing the below drop trigger statement.

drop trigger IT_PROG_range;

Now, the trigger is dropped. Previously we have seen the salary of job_id as IT_PROG. Now, let us see the minimum and maximum salary of each job_id by executing the below query.

select job_id, min(salary), max(salary) from employees Group by job_id order by 1;

So, you can see the minimum and maximum salary for each job_id.

Creating Compound Trigger in Oracle

Now, let us try to create a compound trigger to prevent inserting or updating the salary of employees out to these ranges. So, it depends on the job_id.

Previously we have defined two variables which hold minimum and maximum values. But here we have multiple values and multiple salaries. We have exactly 19 job_id’s. Each job_id has both minimum and maximum salaries. So, in that case, we have to create 38 variables to store each job_id’s minimum and maximum salary values. This is not efficient.

Let us try to create a trigger without a compound trigger by executing the following SQL Statement and see how we can create the trigger for this issue.

create or replace trigger salary_range
before 
insert OR UPDATE
on employees
for each row
declare
v_min_sal number;
v_max_sal number;
begin
   select min(salary), max(salary)
   into v_min_sal,v_max_sal
   from employees
   where job_id=:new.job_id;
   
   if :new.salary not between v_min_sal and v_max_sal then
   raise_application_error (-20300,'invalid range');
   end if;
end;

From the above statement, you can see the trigger name is salary_range and the event is insert or update and the timing is before on the table employees. This trigger is for each row. We have declared two variables for both minimum and maximum values.

The logic of the trigger is the minimum and maximum salary of the given job_id is stored in v_min_sal and v_max_sal. Once the salary is entered then the new salary is compared with v_min_sal and v_max_sal. If the salary is not between minimum and maximum salary then we get an error with the error number 20300. The logic is the same as we have learnt during the mutating table for the job_id=’IT_PROG’. Let’s try to compile this trigger.

The trigger is now compiled successfully. Let’s try to run the update statement for the specific employee_id. Previously we tried to update only employees where job_id was ‘IT_PROG’. Now we are updating the employee’s salary globally by executing the below update statement.

Update employees Set salary=6000 Where employee_id=107;

So, we are getting the mutating error. This is because while the record is being updated, the trigger is reading the record using the select statement. So, we have to solve this issue. Let us try to see all the job_id salary details again by executing the below SELECT SQL Statement.

select job_id, min(salary), max(salary) from employees Group by job_id order by 1;

So, in order to solve this issue, we can use the array to store the minimum and maximum salary details. Now, let us try to create a compound trigger with the array in it which stores the minimum and maximum values of the job_ids by executing the below SQL Statements.

create or replace trigger salary_range
for 
insert OR UPDATE
on employees
compound trigger
  type job_t is record( minsal number,maxsal number);
  type emp_t is table of job_t index by varchar2(100);
  emp emp_t;
 before statement is
   begin
         for i in(
              select job_id, min(salary) min_sal ,max(salary) max_sal
              from employees
              group by job_id order by 1
              )
              loop
              emp(i.job_id).minsal:=i.min_sal;
              emp(i.job_id).maxsal:=i.max_sal;
             end loop;
   end before statement;
   
   before each row is
   begin
   if :new.salary not between emp(:new.job_id).minsal and emp(:new.job_id).maxsal then
   raise_application_error (-20300,'invalid range');
   end if;
   end before each row;
end;

From the above statement. you can see the trigger name is salary_range and the event are insert or update on the table employees. There is no timing here. We have created a compound trigger. We have created two variables job_t where the data type is “is record” where two columns are minsal and maxsal with datatypes as number. The other variable is emp_t which is an array based on the first variable which is of the type where the index data type is varchar2(100). We have declared a variable emp with the datatype emp_t. So, now we can deal with this variable emp.

Coming to the main logic of the code, we will use the two triggers. Before statement trigger and before each row trigger. Now, in the before statement trigger, we will create an index where the index name is emp and the minimum salary will be stored in minsal and the maximum salary will be stored in maxsal variables. Even if we execute the below select statement

select job_id, min(salary) min_sal, max(salary)max_sal from employees group by job_id order by 1

This entire output will be stored in the index emp. This will be the task performed by the before statement trigger. Here the trigger is just storing each job_id’s minimum and maximum salary in the array. This is the same example as the previous one but here we are saving the variables in an array.

Now, coming to each row trigger. This trigger will check the given salary within the array and then display the error if the salary is not in the range. Let’s try to compile the trigger.

So, the salary_range trigger is now compiled. Let us now try to test the trigger by running the below update statement.

Update employees Set salary=6000 Where employee_id=107;

So, now we are getting the correct output and we are not getting the mutating table error. We are getting an invalid range error.

In the next article, I am going to discuss On Delete Cascade in Oracle with Examples. Here, in this article, I try to explain Compound Triggers and Arrays to Solve Mutating in Oracle with Examples. I hope you enjoy this Compound Trigger and Array to Solve Mutating in Oracle with Examples article.