Error with Trigger and exception

Question

The detail is that I am trying to insert a post and fire a Rigger to save the message according to whether or not the post is entered.

The mistake is I can’t capture the exception. When I enter record that there is already a pk, oracle gives the error related to pk integrity, but not saved in the message table.

When there is no id, save normally and save the corresponding message.

set serveroutput on  

create or replace trigger tg_mensagem  
after insert  
on cargos  
for each row  

begin  
insert into mensagens values ('cargo inserido');  

exception when  
others then  
insert into mensagens values ('cargo nao inserido');  
end;  
/  

create or replace procedure insert_cargo(v_id_cargo cargos.id_cargo%type, v_cargo cargos.cargo%type, v_salario_minimo cargos.salario_minimo%type, v_salario_maximo cargos.salario_maximo%type)  
as  

begin  
insert into cargos  
values (v_id_cargo, v_cargo, v_salario_minimo, v_salario_maximo);  

commit;  
end;  
/  

begin  
insert_cargo('GF', 'Gerente financeiro', 400, 500);  
end;  
/  

How do I save the message even when there is inserrsion error because of primary key?

Answer 1

Puts an Exception in your database to capture the PK error, for example:

create or replace procedure insert_cargo(v_id_cargo cargos.id_cargo%type, v_cargo cargos.cargo%type, v_salario_minimo cargos.salario_minimo%type, v_salario_maximo cargos.salario_maximo%type)  
as
begin 
  begin 
    insert into cargos  
    values (v_id_cargo, v_cargo, v_salario_minimo, v_salario_maximo);  
  exception 
    when DUP_VAL_ON_INDEX then
      insert into mensagens values ('cargo nao inserido - PK/Unique violados');
    when OTHERS then
      insert into mensagens values ('cargo nao inserido - Erro desconhecido');      
  end;  
  commit;  
end;

It will not be possible to analyze errors like PK in Rigger, because it is only called after Oracle validates the Insert and the indices (unless of course you enter data into another table in Rigger). On your Trigger after Insert then just have the position message Insert inserted.