Oracle Validation Trigger for range check

Question

A similar question might have been asked, if so please share the link.

Required:

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 and min,max salary ranges.
An Employee table with employee_id, name, job_id and salary.

Below is the trigger,

create or replace TRIGGER secure_employees_sal_range
  BEFORE INSERT OR UPDATE OF salary,job_id ON employees
FOR EACH ROW
DECLARE
p_min_sal Integer;
p_max_sal Integer;
BEGIN
  --check_salaray_range(employee_id,job_id,salary);

  select min_salary, max_salary
  into p_min_sal, p_max_sal
  from jobs
  where job_id = p_job_id;

  if p_salary < p_min_sal OR p_salary > p_max_sal THEN
  RAISE_APPLICATION_ERROR (-20225,
        'Salray should in range' || p_min_sal || ' and ' || p_max_sal);
  END IF;

END secure_employees_sal_range;

But I am getting error

Error at line 13: PL/SQL: Statement ignored

 Line 14: where job_id = :new.job_id;

How can we set the value of p_job_id from employee table?

Update: Now getting the error message when the salary is out of range when creating a new employee

ORA-20225: Salray should in range4000 and 10000 ORA-06512: at "HR.SECURE_EMPLOYEES_SAL_RANGE", line 14 ORA-04088: error during execution of trigger 'HR.SECURE_EMPLOYEES_SAL_RANGE' Unable to process row of table EMPLOYEES.

And when trying to update an existing record then,

ORA-20505: Error in DML: p_rowid=215, p_alt_rowid=EMPLOYEE_ID, p_rowid2=, p_alt_rowid2=. ORA-20225: Salray should in range4000 and 10000 ORA-06512: at "HR.SECURE_EMPLOYEES_SAL_RANGE", line 14 ORA-04088: error during execution of trigger 'HR.SECURE_EMPLOYEES_SAL_RANGE' Unable to process row of table EMPLOYEES.

With your updates, I'm not sure what your question is. You wanted the trigger to throw an exception if the salary was out of range. The trigger is throwing an exception when the salary is out of range. That exception then bubbles up. The code appears to be working as you wished it to. If that's not the behavior you want, you'll have to explain the required behavior in more detail. — Justin Cave
So, it was an exception raised, can I print/return a simple text message? I don't want to print any extra details that are not helpfull to end user. — user3141985
It sounds like a trigger may be the wrong solution for the job. All a trigger can do is throw an exception to stop invalid data from being inserted. That's what your trigger is doing. The code executing the insert or update statement could have an exception handler that catches the exception thrown by the trigger and displays a more meaningful message to the user. — Justin Cave

Justin Cave Justin Cave · Accepted Answer · 2015-06-30T21:37:58

Triggers don't have parameters.

My guess is that you want a row-level trigger, rather than the statement-level trigger that you have here. I'm guessing that within the row-level trigger that you'd want to use the :new.job_id to look up the data in the jobs table

create or replace TRIGGER secure_employees_sal_range
  BEFORE INSERT OR UPDATE OF salary,job_id ON employees
  FOR EACH ROW
DECLARE
  p_min_sal Integer;
  p_max_sal Integer;
BEGIN
  --check_salaray_range(employee_id,job_id,salary);

  select min_salary, max_salary
    into p_min_sal, p_max_sal
    from jobs
   where job_id = :new.job_id;

  if p_salary < p_min_sal OR p_salary > p_max_sal THEN
    RAISE_APPLICATION_ERROR (-20225,
        'Salary should in range' || p_min_sal || ' and ' || p_max_sal);
  END IF;
END secure_employees_sal_range;

I'm assuming that this is a homework assignment. In reality, in a multi-user system, this sort of trigger has a variety of issues. For example, someone may be modifying the jobs table at the same time that you're modifying the employees table and neither transaction would be able to see the uncommitted work of the other. Even if you have a corresponding trigger on jobs, it's entirely possible to end up with saved data that violates the range rule you're trying to enforce.

As a general rule, I would also suggest that local variables use the l_ prefix rather than p_ which generally denote parameters. I would also generally suggest that you use anchored types. So

l_min_sal jobs.min_salary%type;

That way, if the data type in the jobs table changes, your code automatically adapts.

Oracle Validation Trigger for range check

1 Answers