Validation trigger for range - Oracle

Question

Whenever a new record is created or updated, a validation is required that the salary of the person is in range according to job.

A Job table with job_id, job_title, min_salary, max_salary fields. An Employee table with employee_id, name, job_id and salary.

this is the trigger i'm trying to do:

CREATE OR REPLACE TRIGGER  range_valid
  BEFORE INSERT OR UPDATE
  OF JOB_ID, SALARY ON EMPLOYEES
  FOR EACH ROW
  DECLARE
     l_min_salary INTEGER;
     l_max_salary INTEGER;
     l_salary INTEGER;
  BEGIN
     SELECT MIN_SALARY, MAX_SALARY
     INTO l_min_salary, l_max_salary
     FROM JOBS
     WHERE JOB_ID = :new.JOB_ID;

  IF l_salary < l_min_salary OR l_salary > l_max_salary THEN
    RAISE_APPLICATION_ERROR (
        'Salario debe estar en el rango de' || l_min_salary || ' and ' || l_max_salary);
 END IF;

END range_valid;

but it shows me the error:

Error(9,5): PL/SQL: Statement ignored

Error(9,5): PLS-00201: The identifier 'L_SALARY' must be declared

I don't know how to fix it.

You declared l_salary but no value was assigned to it.Perhaps you want to compare :new.salary with l_min_salary with l_max_salary — Suresh
Please don't paste images, use formatted text for code and errors. — Wernfried Domscheit
Use Alter session set nls_language = 'english' in order to get the error message in English. — Wernfried Domscheit
comment out l_salary and replace l_salary with :new.salary in if clause — Suresh

D. Mika D. Mika · Accepted Answer · 2021-04-17T08:51:04

You don't need to declare l_salary and using it without assigning a value doesn't makes sense either ;-).

Assuming the employees table contains a column salary the trigger should look like:

CREATE OR REPLACE TRIGGER range_valid
   BEFORE INSERT OR UPDATE OF job_id, salary
   ON employees
   FOR EACH ROW
DECLARE
   l_min_salary   INTEGER;
   l_max_salary   INTEGER;
BEGIN
   SELECT min_salary, max_salary
     INTO l_min_salary, l_max_salary
     FROM jobs
    WHERE job_id = :new.job_id;

   IF    :new.salary < l_min_salary
      OR :new.salary > l_max_salary
   THEN
      raise_application_error('Salario debe estar en el rango de' || l_min_salary || ' and ' || l_max_salary);
   END IF;
END range_valid;
/

Validation trigger for range - Oracle

1 Answers