Mutating triggers / tables
When something mutates, it is changing. Something that is
changing is hard to analyze and to quantify.
A mutating table error (ORA-04091) 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).
In particular, this error occurs when a row-level trigger
attempts to read or write the table from which the trigger was fired.
Fortunately, the same restriction does not apply in statement-level triggers.
CREATE OR REPLACE TRIGGER equitable_salary_trg
AFTER
INSERT OR UPDATE
ON
employees
FOR EACH
ROW
DECLARE
l_max_allowed
employees.salary%TYPE;
BEGIN
SELECT MIN
(salary) * 25
INTO
l_max_allowed
FROM
employees;
IF
l_max_allowed < :NEW.salary
THEN
UPDATE
employees
SET
salary = l_max_allowed
WHERE
employee_id = :NEW.employee_id;
END IF;
END equitable_salary_trg;
Execute the following statement to check if the trigger
works
BEGIN UPDATE employees SET salary = 100000 WHERE last_name = 'King'; END;
OK, we get that, right? I am both selecting from and trying
to update the EMPLOYEES table in a row-level trigger. That's the no-no.
Getting Around ORA-04091 with PL/SQL Packages
Comments
keep it up
Thank You