Using Procedure in Trigger in PL/SQL

Question

Well I have this Procedure:

SET SERVEROUTPUT ON;
CREATE OR REPLACE 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);
END IF;
END;

This PROCEDURE has two parameters. It checks if the salary of a job_id is between the max and the min of the job.

Now I want to create a TRIGGER.

If INSERT or UPDATE is called on employees I want to execute the prodcedure. But I don't know what I should write as a parameter in the procedure

CREATE OR REPLACE TRIGGER check_salary_trg 
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
DECLARE
BEGIN
check_salary(); --Don't know which parameter I should use
END;

Pass :new.job_id and :new.salary but I am not sure if you will get table mutating error or not — Popeye
– Popeye, Commented Jan 5, 2021 at 12:34

Julissa DC · Accepted Answer · 2021-01-05 15:30:16Z

2

You should use :NEW as New Row and :OLD as Old Row. But I think it is not useful to create a trigger as BEFORE INSERT. You may create a trigger as AFTER INSERT OR UPDATE. Here is an example :

CREATE OR REPLACE TRIGGER check_salary_trg
AFTER INSERT OR UPDATE ON EMPLOYEE
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
DECLARE
BEGIN
   check_salary(:NEW.job_id);

   EXCEPTION
     WHEN OTHERS THEN
       -- Consider logging the error and then re-raise
       RAISE;
END check_salary_trg;

edited Jan 5, 2021 at 15:30

Julissa DC

2593 silver badges14 bronze badges

answered Jan 5, 2021 at 12:43

user14894012

Sign up to request clarification or add additional context in comments.

Comments

Julissa DC · Accepted Answer · 2021-01-05 12:44:31Z

0

You just need to add the job_id and the salary

 CREATE OR REPLACE TRIGGER check_salary_trg 
    BEFORE INSERT OR UPDATE ON employees
    FOR EACH ROW
    DECLARE
    BEGIN
    check_salary(:new.job_id, :new.salary);
    END;

answered Jan 5, 2021 at 12:44

Julissa DC

2593 silver badges14 bronze badges

3 Comments

Wernfried Domscheit Over a year ago

The syntax is correct, however you will get an ORA-04091: table HR.employees is mutating, trigger/function may not see it error

yen Over a year ago

@WernfriedDomscheit And how do I avoid that error?

Wernfried Domscheit Over a year ago

You would need a Compound Trigger or preferably encabsulate all in a stored PL/SQL procedure.

Collectives™ on Stack Overflow

Using Procedure in Trigger in PL/SQL

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related