PL/SQL Trigger doesn't work on brand new inserted data-CodePudding

I've created a trigger to check if someone updated a salary or inserted a new employee on the employees table using the procedure for checking, below is my code for my procedure and my trigger:

CREATE OR REPLACE PROCEDURE check_salary (pjobid employees.job_id%type, psal employees.salary%type)
IS

BEGIN
    
    FOR i in (SELECT min_salary, max_salary
                FROM jobs
                WHERE job_id = pjobid)
    LOOP
        IF psal < i.min_salary OR psal > i.max_salary THEN
            RAISE_APPLICATION_ERROR(-20001, 'Invalid salary ' || psal || '. Salaries for job ' || pjobid || ' must be between ' || i.min_salary || ' and ' || i.max_salary);
        ELSE
            DBMS_OUTPUT.PUT_LINE('Salary is okay!');
        END IF;
    END LOOP;

END check_salary;

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

Now it works normally when i try to update the salary of the specific employee that is already declared on the table (result is the one that is commented):

UPDATE employees
    SET salary = 2800
WHERE employee_id = 115;
--ORA-20001: Invalid salary 2800. Salaries for job HR_REP must be between 4000 and 9000

But, I'm wondering why it won't work when i have manually inserted the data when the insert was already declared on the trigger

INSERT INTO employees(first_name, last_name, email, department_id, job_id, hire_date) VALUES ('Lorem', 'Ipsum', 'loremipsum', 30, 'SAL_REP', TRUNC(sysdate));

OR when i use my add_employee procedure that is inside on my package, below is the code:

PROCEDURE ADD_EMPLOYEE(vfirstname        employees.first_name%type,
                     vlastname       employees.last_name%type,
                     vdepid      employees.department_id%type,
                     vjob        employees.job_id%type := 'SA_REP')
    IS
        vemail  employees.email%type;
    BEGIN
        vemail := upper(substr(vfirstname, 1, 1)) || upper(substr(vlastname,1,7));
        IF valid_deptid(vdepid) THEN
            INSERT INTO employees(first_name, last_name, email, department_id, job_id, employee_id, hire_date)
            VALUES(vfirstname, vlastname, vemail, vdepid, vjob, employees_seq.nextval, TRUNC(SYSDATE));
            COMMIT;
        ELSE
            RAISE_APPLICATION_ERROR(-20003, 'Invalid Department ID');
        END IF;
    END ADD_EMPLOYEE;

PROCEDURE ADD_EMPLOYEE(vfirstname       employees.first_name%type,
                     vlast_name      employees.last_name%type,
                     vemail      employees.email%type,
                     vjob        employees.job_id%type := 'SA_REP',
                     vmgr        employees.manager_id%type := 145,
                     vsalary         employees.salary%type := 1000,
                     vcomm       employees.commission_pct%type := 0,
                     vdepid      employees.department_id%type := 30)
    IS
    BEGIN
        IF valid_deptid(vdepid) THEN
            INSERT INTO employees(first_name, last_name, email, department_id, job_id, manager_id, salary, commission_pct, employee_id, hire_date)
            VALUES(vfirstname, vlast_name, vemail, vdepid, vjob, vmgr, vsalary, vcomm, employees_seq.nextval, TRUNC(SYSDATE));
            COMMIT;
        ELSE
            RAISE_APPLICATION_ERROR(-20003, 'Invalid Department ID');
        END IF;
    END ADD_EMPLOYEE;

CodePudding user response：

Congrats on the well documented question.

The issue is with the WHEN clause of the trigger. On insert the old value is NULL and in oracle, you can't compare to NULL using "=" or "!=".

Check this example:

PDB1--KOEN>create table trigger_test 
  2  (name VARCHAR2(10));

Table TRIGGER_TEST created.

PDB1--KOEN>CREATE OR REPLACE trigger trigger_test_t1
  2  BEFORE INSERT OR UPDATE ON trigger_test
  3  FOR EACH ROW
  4  WHEN (new.name != old.name) 
  5  BEGIN
  6    RAISE_APPLICATION_ERROR(-20001, 'Some error !');
  7  END trigger_test_t1;
  8  /

Trigger TRIGGER_TEST_T1 compiled

PDB1--KOEN>INSERT INTO trigger_test (name) values ('koen');

1 row inserted.

PDB1--KOEN>UPDATE trigger_test set name = 'steven';

Error starting at line : 1 in command -
UPDATE trigger_test set name = 'steven'
Error report -
ORA-20001: Some error !
ORA-06512: at "KOEN.TRIGGER_TEST_T1", line 2
ORA-04088: error during execution of trigger 'KOEN.TRIGGER_TEST_T1'

That is exactly the behaviour you're seeing in your code. On insert the trigger doesn't seem to fire. Well... it doesn't because in oracle, 'x' != NULL yields false. See info at the bottom of this answer. Here is the proof. Let's recreate the trigger with an NVL function wrapped around the old value.

PDB1--KOEN>CREATE OR REPLACE trigger trigger_test_t1
  2  BEFORE INSERT OR UPDATE ON trigger_test
  3  FOR EACH ROW
  4  WHEN (new.name != NVL(old.name,'x'))
  5  -- above is similar to this
  6  --WHEN (new.name <> old.name or
  7  --     (new.name is null and old.name is not NULL) or
  8  --     (new.name is not null and old.name is NULL) )
  9  BEGIN
 10    RAISE_APPLICATION_ERROR(-20001, 'Some error !');
 11  END trigger_test_t1;
 12  /

Trigger TRIGGER_TEST_T1 compiled


PDB1--KOEN>INSERT INTO trigger_test (name) values ('jennifer');

Error starting at line : 1 in command -
INSERT INTO trigger_test (name) values ('jennifer')
Error report -
ORA-20001: Some error !
ORA-06512: at "KOEN.TRIGGER_TEST_T1", line 2
ORA-04088: error during execution of trigger 'KOEN.TRIGGER_TEST_T1'

There you go. It now fires on insert.

Now why is this happening ? According to the docs: Because null represents a lack of data, a null cannot be equal or unequal to any value or to another null. However, Oracle considers two nulls to be equal when evaluating a DECODE function. Check the docs or read up on it in this 20 year old answer on asktom

CodePudding user response：

@KoenLostrie is absolutely correct as to Why your trigger does not fire on Insert. But that is just half the problem. But, the other issue stems from the same misconception: NULL values The call to check_salary passes :old.job_id but it is still null, resulting in cursor ( for i in (Select ...)) returning no rows when it attempts 'WHERE job_id = null`. However there is no exception then a cursor returns no rows, the loop is simply not entered. You need to pass ':new.job_id'. You would also want the new job id on Update as well. Image an employee gets a promotion the update is like to be something like:

update employee 
   set job_id = 1011 
     , salary = 50000.00 
 where employee = 115;

Finally, processing a cursor is at dangerous at best. Doing so at lease implies you allow multiple rows in the Jobs for a given job_id. What happens when those rows have different min_salary and max_salary You can update the procedure or just do everything in the trigger and eliminate the procedure.

create or replace trigger check_salary_trg
   before insert or update on employees
      for each row
declare
    e_invalid_salary_range; 
    l_job  jobs%rowtype; 
begin
    select *
      from jobs
      into l_job
     where job_id = :new.job_id; 
     
     if :new.salary < l_job.min_salary 
     or :new.salary > l_job.max_salary
     then 
         raise  e_invalid_salary_range; ; 
     end if; 
 
exception 
    when e_invalid_salary_range then 
          raise_application_error(-20001, 'Invalid salary ' || psal || 
                                  '. Salaries for job ' || pjobid || 
                                  ' must be between ' ||  l_job.min_salary || 
                                  ' and ' ||  l_job.max_salary
                                  );
                           
end check_salary_trg;

You could add handling no_data_found and too_many_rows the the exception block, but those are better handled with constraints.