PL / SQL - Trigger: Can I see the entire row updated in a row after trigger?

1

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.

    
asked by anonymous 12.11.2017 / 07:15

0 answers