Well I'm trying to call a stored procedure in my trigger.
The error I'm getting
"table %s.%s is mutating, trigger/function may not see it"
*Cause: A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.
*Action: Rewrite the trigger (or function) so it does not read that table.
This is my code of the trigger:
create or replace TRIGGER check_salary_trg
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(:new.job_id ||' '|| :new.salary);
check_salary(:new.job_id, :new.salary);
END;
This is my stored procedure:
PROCEDURE check_salary (job_id employees.job_id%TYPE, salary employees.salary%TYPE)
IS
maxSal NUMBER;
minSal NUMBER;
BEGIN
SELECT MAX(salary) INTO maxSal FROM employees WHERE job_id = job_id;
SELECT MIN(salary) INTO minSal FROM employees WHERE job_id = job_id;
IF maxSal >= salary OR minSal <= salary THEN
RAISE_APPLICATION_ERROR(-20001,'Invalid slary '||salary||'. Salaries for job '||job_id||' must be between '||minSal||' and '||maxSal);
ELSE
DBMS_OUTPUT.PUT_LINE('Test');
END IF;
END;
This is how I try to see that the trigger is working:
UPDATE employees SET salary = 100000 WHERE employee_id = 100;
Somehow the DBMS_OUTPUT.PUT_LINE
in my Trigger code is working. But the stored procedure causes the error.
question from:
https://stackoverflow.com/questions/65601646/call-a-stored-procedure-in-a-trigger-pl-sql 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…