CODEWITHSUNDEEP
oracle-databaseoracle11gtriggers
user3141985
user3141985

Reputation: 1385

Oracle Validation Trigger for range check

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.

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.

Upvotes: 1

Views: 2376

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

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.

Upvotes: 2

Related Questions