I have a table employee with columns id,name,designation,and salary.
also another table designation_emp with columns designation and low_salary and high_salary.
I need to fire a trigger when ever salary of employee table is updated which insert the old and new salary of the employee in another table.
until this is fine...
but the problem statement is, whenever the salary changes we need check the corresponding designation in the designation_emp table and then update the designation in employee table.
create or replace
BEFORE UPDATE OF SALARY ON EMPLOYEE for each row
old_sal number(10):= :OLD.salary;
new_sal number(10):= :NEW.salary;
id number:= :OLD.emp_id;
select designation into new_designation from DESIGNATION_EMP where low_limit<=new_sal and high_limit>=new_sal;
insert into salary_log(emp_id,GRADE,OLD_SALARY,NEW_SALARY) values(id,new_grade,old_sal,new_sal);
update employee set designation=new_designation;
Initially is giving mutating of tables error so I added pragma autonomous transaction and commit statements
now it is giving deadlock detected while waiting for response error.
Can you please tell how to modify this so that the problem statement will satisfied.