PLS-00049 bad bind variable 'NEW.NUM_CUENTA'

Question

I'm doing this trigger in Oracle for a view called empleado but I'm having this error

18/40 PLS-00049 bad bind variable 'NEW.NUM_CUENTA'

Can someone help me showing me what I am doing wrong

Trigger is:

create or replace trigger t_dml_empleado
instead of insert or delete on empleado
declare

begin 
    case 
        when inserting then 
            if (SUBSTR(rfc, 1,1) BETWEEN 'A' and 'M') then 
                insert into empleado_2 (empleado_id, nombre, ap_paterno, ap_materno, rfc, email, jefe_id)
                values(:new.empleado_id, :new.nombre, :new.ap_paterno, :new.ap_materno, 
                        :new.rfc, :new.email, :new.jefe_id);
            elsif (SUBSTR(rfc, 1,1) BETWEEN 'N' and 'Z')    then 
                insert into empleado_3 (empleado_id, nombre, ap_paterno, ap_materno, rfc, email, jefe_id)
                values (:new.empleado_id, :new.nombre, :new.ap_paterno, :new.ap_materno,
                        :new.rfc, :new.email, :new.jefe_id);
            else
                raise_application_error(20001,'Valor incorrecto para RFC: '|| :new.rfc);
            end if;
            insert into empleado_1(empleado_id, foto, num_cuenta)
            values (:new.empleado_id, :new.foto,:new.num_cuenta);

        when deleting then 
            if (SUBSTR(rfc, 1,1) BETWEEN 'A' and 'M')  then 
                delete from empleado_2 where empleado_id =:old.empleado_id;
            elsif (SUBSTR(rfc, 1,1) BETWEEN 'N' and 'Z')    then
                delete from empleado_3 where empleado_id = :old.empleado_id;            
            else
                raise_application_error(20001,'Valor incorrecto para RFC: '|| :new.rfc);
            end if;
            delete from empleado_1 where empleado_id = :old.empleado_id;
    end case;
end;

Explaining empleado in the second line is a view, which is in a PDB:

create or replace view empleado as
    select q1.empleado_id, q1.nombre, q1.ap_paterno, q1.ap_materno, q1.rfc,q1.email, q1.jefe_id, foto
    from (
            select empleado_id, nombre, ap_paterno, ap_materno, rfc,email, jefe_id 
            from empleado_2
            union
            select empleado_id, nombre, ap_paterno, ap_materno, rfc,email, jefe_id 
            from empleado_3
         ) q1,(select empleado_id, foto, num_cuenta 
                   from empleado_1) q2
            where q1.empleado_id=q2.empleado_id;

That is from three tables empleado_3, empleado_1 is in one PDB, and empleado_2 is in another PDB. The view is making JOIN of remote tables.

I created synonyms so the problem is not there. The tables are:

CREATE TABLE F_AMG_EMPLEADO_1
(
    EMPLEADO_ID          NUMERIC(10,0) NOT NULL ,
    FOTO                 BLOB NOT NULL ,
    NUM_CUENTA           VARCHAR2(18) NOT NULL 
);
CREATE TABLE F_AMG_EMPLEADO_3
(
    EMPLEADO_ID          NUMERIC(10,0) NOT NULL ,
    NOMBRE               VARCHAR2(40) NOT NULL ,
    AP_PATERNO           VARCHAR2(40) NOT NULL ,
    AP_MATERNO           VARCHAR2(40) NOT NULL ,
    RFC                  VARCHAR2(13) NOT NULL ,
    EMAIL                VARCHAR2(40) NOT NULL ,
    JEFE_ID              NUMERIC(10,0) NULL 
);

Answer 1

In a view empleado you do not have a column named num_cuenta. You have to select it in a view as I have did in my DEMO.

Next thing that is wrong is use of case when in the trigger for WHEN INSERTING and WHEN DELETING. I have changed that with if then elsif

Next thing that is not ok is use of the rfc in SUBSTR function. You have to reference to a :new or :old value as I have in my DEMO. Here is the trigger that worked:

create or replace trigger t_dml_empleado
instead of insert or delete on empleado
declare

begin 
    if inserting then 
            if (SUBSTR(:new.rfc, 1,1)) BETWEEN 'A' and 'M' then
               insert into empleado_2 (empleado_id, nombre, ap_paterno, ap_materno, rfc, email, jefe_id)
                values(:new.empleado_id, :new.nombre, :new.ap_paterno, :new.ap_materno, 
                        :new.rfc, :new.email, :new.jefe_id);
            elsif (SUBSTR(:new.rfc, 1,1) BETWEEN 'N' and 'Z') then 
                insert into empleado_3 (empleado_id, nombre, ap_paterno, ap_materno, rfc, email, jefe_id)
                values (:new.empleado_id, :new.nombre, :new.ap_paterno, :new.ap_materno,
                        :new.rfc, :new.email, :new.jefe_id);
             else
                raise_application_error(20001,'Valor incorrecto para RFC: '|| :new.rfc);
            end if;
    elsif deleting then 
            if (SUBSTR(:old.rfc, 1,1)) BETWEEN 'A' and 'M'  then 
                delete from empleado_2 where empleado_id =:old.empleado_id;
            elsif (SUBSTR(:new.rfc, 1,1) BETWEEN 'N' and 'Z')    then
                delete from empleado_3 where empleado_id = :old.empleado_id;            
            else
                raise_application_error(20001,'Valor incorrecto para RFC: '|| :new.rfc);
            end if;
            delete from empleado_1 where empleado_id = :old.empleado_id;
    end if;
end;
/

Here is the DEMO. I hope this will help you resolve your problem. Cheers!

Answer 2

Here is the projection of your view:

select q1.empleado_id, q1.nombre, q1.ap_paterno, q1.ap_materno, q1.rfc,q1.email, q1.jefe_id, foto

Those are the only columns you can reference in trigger t_dml_empleado built on that view. As you can see, there is no mention of num_cuenta in the view's projection. Consequently you cannot reference :new.num_cuenta in the trigger.

As for solution, either you change the trigger or you add num_cuenta to the columns selected in the view's definition.