What is Mutating Table Errors ?

A mutating table error occurs when a row-level trigger tries to examine or change a table that is already undergoing change (via an INSERT, UPDATE, or DELETE statement).

sql>CREATE OR REPLACE FUNCTION rec_call(sal NUMBER)

RETURN NUMBER IS

BEGIN

INSERT INTO employees(employee_id, last_name, email, hire_date, job_id, salary)

VALUES(1, 'Sharma', 'sharma@gmail.com',

SYSDATE, 'SA_REP', sal);

RETURN (sal + 100);

END;

Function created.


sql>UPDATE employees

SET salary = Rec_call(2000)

WHERE employee_id = 197

Note:- The Rec_call function contains an INSERT statement that inserts a new record into the EMPLOYEES table and returns the input salary value incremented by 100. This function is invoked in the UPDATE statement that modifies the salary of employee 197 to the amount returned from the function.The UPDATE statement fails with an error indicating that the table is mutating (that is, changes are already in progress in the same table). In the following example, the query_call_sql function queries the SALARY column of the EMPLOYEES table