CODEWITHSUNDEEP
oracle-databaseplsql
yen
yen

Reputation: 17

Using Procedure in Trigger in PL/SQL

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;

Upvotes: 0

Views: 405

Answers (2)

user14894012
user14894012

Reputation:

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;

Upvotes: 2

Julissa DC
Julissa DC

Reputation: 259

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;

Upvotes: 0

Related Questions