Error with trigger and exception

Question

Error with trigger and exception

Navigation

#1 by (1 votes)

0

The detail is that I'm trying to insert a post and trigger a trigger to save the message according to whether the post is inserted or not.

The error is that I can not capture the exception. When I enter a record that already exists a pk, oracle gives the error related to the integrity of pk, but does not save in the message table.

When the id does not exist, it normally saves and saves 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 the insertion error occurs because of the primary key?

pl-sql

asked by anonymous 05.10.2018 / 03:13

1 answer

Input disable not found in serialize The requested file could not be loaded: products / list_table.php

score 1 · Answer 1

Puts an exception in your procedure 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 the trigger, because it is only called after Oracle validates the insert and indices (unless you insert data into another table in the trigger). In your trigger after insert then just have the insert of the cargo message inserted.