I am studying PL / SQL in college and the teacher has passed some questions based on Oracle's own Human Resources model. The question I am asking asks for a trigger that prevents an employee from having a salary greater than his boss. And for this trigger I need a package with some procedures not to give a mutant trigger error.
This was the package I wrote:
CREATE OR REPLACE PACKAGE BODY CONSULT_EMPLOYEE_SALARY IS
PROCEDURE LIMPA_VETOR IS
BEGIN
idx := 0;
v_tab.DELETE;
END;
PROCEDURE ALIMENTAR_VETOR (p_id EMPLOYEES.EMPLOYEE_ID%TYPE, p_salary EMPLOYEES.SALARY%TYPE, p_managerID EMPLOYEES.MANAGER_ID%TYPE) IS
BEGIN
idx := idx + 1;
v_tab(idx).EMPLOYEE_ID := p_id;
v_tab(idx).SALARY := p_salary;
v_tab(idx).MANAGER_ID := p_managerID;
END;
PROCEDURE OBTER_VETOR (p_idx PLS_INTEGER, p_employeeID OUT EMPLOYEES.EMPLOYEE_ID%TYPE, p_salary OUT EMPLOYEES.SALARY%TYPE, p_managerID OUT EMPLOYEES.MANAGER_ID%TYPE) IS
BEGIN
IF v_tab.EXISTS(p_idx) THEN
p_employeeID := v_tab(idx).EMPLOYEE_ID;
p_salary := v_tab(idx).SALARY;
p_managerID := v_tab(idx).MANAGER_ID;
END IF;
END;
END;
And this is the trigger that executes the procedure FEED_VECTOR:
CREATE OR REPLACE TRIGGER EMPLOYEE_AU AFTER UPDATE ON EMPLOYEES
FOR EACH ROW
BEGIN
IF UPDATING THEN
CONSULT_EMPLOYEE_SALARY.ALIMENTAR_VETOR(:OLD.EMPLOYEE_ID, :NEW.SALARY, :OLD.MANAGER_ID) ;
END IF;
END;
When I test the update the following error occurs:
Erro a partir da linha : 249 no comando -
update employees set salary = 9500 where employee_id = 104
Relatório de erros -
ORA-06502: PL/SQL: erro: valor-chave de tabela de índice NULL numérico ou de
valor
ORA-06512: em "HR.CONSULT_EMPLOYEE_SALARY", line 12
ORA-06512: em "HR.EMPLOYEE_AU", line 3
ORA-04088: erro durante a execução do gatilho 'HR.EMPLOYEE_AU'
I researched this error, but the message I found related to this ORA code is different. Is this saying that I am passing a null parameter? And I'm also unsure if it's possible to access all columns in an after-line trigger using: old. [Column-name].
Thank you.