Error while executing insert triggers in PL/SQL

Question

I am getting following error while executing PL/SQL here we take 2 variables for storing salary 'x' and 'y'.

Error report -
ORA-04091: table SXP180107.EMPLOYEE is mutating, trigger/function may not see it
ORA-06512: at "SXP180107.CHECK_SAL", line 5
ORA-04088: error during execution of trigger 'SXP180107.CHECK_SAL'



create TRIGGER Check_sal
 AFTER INSERT OR UPDATE OF Salary ON EMPLOYEE
FOR EACH ROW
DECLARE
 X NUMBER;
 Y NUMBER;
 BEGIN
 select SALARY into X from Employee where SSN=:NEW.SSN;
 select SALARY into Y from Employee E, Department D where E.Dno = D.Dno and E.Ssn = D.Mgrssn;

 IF (X > Y)
 THEN
   dbms_output.put_line('error');
 -- RAISE_APPLICATION_ERROR( -20001, 'The new Salary shouldnt be greater than mgr salary' );
 END IF;
END;




insert into employee values('John','B','Smith','123456700',TO_DATE('1955-01-9','YYYY-MM-DD'),'731 Fondren, Houston, TX','M',100,'333445555',5);

While i am trying to insert data i am getting those errors.

score 1 · Answer 1 · edited Oct 11 '19 at 20:31

I'm not sure I'd like to work for that employer. My salary can only be lower and lower.

Anyway: why not just

create or replace trigger check_sal
  before update of salary on employee
  for each row
begin
  if :new.salary > :old.salary then
     raise_application_error(-20001, 'The new Salary shouldnt be greater than old Salary');
  end if;
end;

You can't query table which is being subject of modification, but you can use pseudorecords.

Moreover, the second select doesn't make much sense. I suppose it'll raise too-many-rows anyway as it isn't restricted enough.

[EDIT, after reading comments]

2nd select is used to find manager salary and if employee salary is greater than mgr salary it raises an error.

Why didn't you say so in the question? The way you initially put it, it looked as if employee's new salary can't be higher than their own old salary.

OK then, here's the revised code. See if it helps. Because of mutating table error, I'm using a compound trigger.

Test table:

SQL> create table employee as select deptno, empno, ename, mgr, sal from emp;

Table created.

Trigger:

SQL> create or replace trigger check_sal
  2    for insert or update on employee
  3    compound trigger
  4
  5    type t_ch_tab is table of employee%rowtype;
  6    g_ch_tab      t_ch_tab := t_ch_tab();
  7
  8    after each row is
  9    begin
 10      g_ch_tab.extend;
 11      g_ch_tab(g_ch_tab.last).empno  := :new.empno;
 12      g_ch_tab(g_ch_tab.last).mgr    := :new.mgr;
 13      g_ch_tab(g_ch_tab.last).sal    := :new.sal;
 14    end after each row;
 15
 16
 17    after statement is
 18      l_sal employee.sal%type;
 19    begin
 20      for i in g_ch_tab.first .. g_ch_tab.last loop
 21        select e.sal
 22          into l_sal
 23          from employee e
 24          where e.empno = g_ch_tab(i).mgr;
 25
 26        if g_ch_tab(i).sal > l_sal then
 27           raise_application_error(-20001, 'Employee''s salary can not be higher than manager''s salary');
 28        end if;
 29      end loop;
 30    end after statement;
 31  end check_sal;
 32  /

Trigger created.

Testing: table contents:

SQL> select * From employee order by deptno, ename;

    DEPTNO      EMPNO ENAME             MGR        SAL
---------- ---------- ---------- ---------- ----------
        10       7782 CLARK            7839       2450
        10       7839 KING                       10000
        10       7934 MILLER           7782       1300
        20       7876 ADAMS            7788       1100
        20       7902 FORD             7566       3000     --> manager
        20       7566 JONES            7839       2975
        20       7788 SCOTT            7566       3000     
        20       7369 SMITH            7902        920     --> employee
        30       7499 ALLEN            7698       1600
        30       7698 BLAKE            7839       2850
        30       7900 JAMES            7698        950
        30       7654 MARTIN           7698       1250
        30       7844 TURNER           7698       1500
        30       7521 WARD             7698       1250

14 rows selected.

Let's update & insert (fails first):

SQL> -- SMITH works in DEPTNO = 20 whose MGR = FORD and his salary is 3000. No employee whose
SQL> -- boss is FORD should have salary higher than 3000
SQL>
SQL> update employee set sal = 10000 where ename = 'SMITH';
update employee set sal = 10000 where ename = 'SMITH'
       *
ERROR at line 1:
ORA-20001: Employee's salary can not be higher than manager's salary
ORA-06512: at "SCOTT.CHECK_SAL", line 25
ORA-04088: error during execution of trigger 'SCOTT.CHECK_SAL'


SQL>
SQL> insert into employee(deptno, empno, ename, mgr, sal) values
  2  (20, 1, 'LF', 7902, 10000);
insert into employee(deptno, empno, ename, mgr, sal) values
            *
ERROR at line 1:
ORA-20001: Employee's salary can not be higher than manager's salary
ORA-06512: at "SCOTT.CHECK_SAL", line 25
ORA-04088: error during execution of trigger 'SCOTT.CHECK_SAL'

Success (as salary is lower than 3000):

SQL> update employee set sal = 2000 where ename = 'SMITH';

1 row updated.

2nd select is used to find manager salary and if employee salary is greater than mgr salary it raises an error. — SAI TEJA, Oct 11 '19 at 07:18
But of course, @a_horse! What was I thinking ... that's what the OP was complaining about initially. Bah! Thank you. Hopefully - fixed. — Littlefoot, Oct 11 '19 at 08:11
my employee and department are 2 different tables and i want to trigger when employee is updated — SAI TEJA, Oct 11 '19 at 21:33

Error while executing insert triggers in PL/SQL

1 Answers1