Hi,
Could you please give the solution for the mutating table errore while using triggers. I will give the simple scenario here,
I am creating a trigger on employees table, whenever any DML operations takes place in employees table, it should execute the trigger body. I have intentionally used the employees table in the trigger body also. Please provide me the solution.
Trigger:
create or replace trigger test_trigger
before insert or update or delete on employees
for each row
declare
a employees.first_name%type;
begin
select first_name into a from employees where job_id ='AD_PRES';
end;
DML statement :
update employees set salary =20000 where job_id='AD_PRES' (I execute this query)
Error message:
ORA-04091: table HR.EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "HR.TEST_TRIGGER", line 4
ORA-04088: error during execution of trigger 'HR.TEST_TRIGGER'
Can anyone tell , in what other scenarios, we will get this mutating table error or recursive error?
Thanks in advance