Ora-06512 and Ora-4088 na Trigger

[rodolpho.netto]

Pessoal, criei uma trigger para um trabalho, que deve verificar se o registro existe e cadastrar ou não o usuario, o mesmo até funciona, porem quando da erro (usuario existente) ele me da dois erros a mais o 6512 e o 4088, seguem anexos os codigos, obrigado!

Selecionar tudo

CREATE TABLE ALUNO
(
	ID_ALUNO             NUMBER NOT NULL ,
	NM_ALUNO             VARCHAR2(30) NOT NULL ,
	SB_ALUNO             VARCHAR2(20) NOT NULL ,
	CD_CPF               VARCHAR2(14) NOT NULL ,
	CD_RG                VARCHAR2(20) NULL ,
	TP_VESTIBULAR        NUMBER(1) NOT NULL 
)

CREATE OR REPLACE PROCEDURE PROC_CAD_ALUNO(
P_NOME IN VARCHAR2,
P_SOBRENOME IN VARCHAR2,
P_CPF IN VARCHAR2,
P_RG IN VARCHAR2,
P_VESTIBULAR IN NUMBER
)
IS
V_CODIGO NUMBER;
BEGIN
SELECT SEQ_ALUNO.NEXTVAL
INTO V_CODIGO
FROM DUAL;
INSERT INTO ALUNO (ID_ALUNO,NM_ALUNO, SB_ALUNO, CD_CPF, CD_RG, TP_VESTIBULAR)
VALUES (V_CODIGO, P_NOME, P_SOBRENOME, P_CPF, P_RG, P_VESTIBULAR);
DBMS_OUTPUT.PUT_LINE ('0');
END;
/

CREATE OR REPLACE TRIGGER TG_BI_ALUNO
BEFORE INSERT
ON ALUNO
FOR EACH ROW
DECLARE
QUANTIDADE NUMBER;
BEGIN
SELECT COUNT(*) INTO QUANTIDADE FROM ALUNO WHERE CD_CPF = :NEW.CD_CPF OR CD_RG = :NEW.CD_RG;
IF QUANTIDADE >= 1
THEN
raise_application_error(-20001,'Codigo do Erro 5000');
END IF;
END;
/

[stcoutinho]

Olá Rodolfo,

Bem vindo ao forum GLUFKE !

Seria melhor se você não usasse a trigger para fazer o "trabalho de integridade" do banco de dados.

Você poderia - por exemplo - criar um índice do tipo UNIQUE para a coluna CD_CPF e outro para CD_RG.

Agora, note que sua trigger na tabela ALUNO faz um SELECT na própria tabela ALUNO. Isso geralmente pode ocasionar um erro de MUTATING TABLE. Ou seja, você não pode usar uma trigger em uma tabela "A" onde a trigger faz um acesso aos próprios registros da tabela "A".

Não sei se consigo ser claro, mas você está tentando fazer uma leitura consistente em uma tabela que está sendo afetada pela própria execução da trigger.

Se desejar seguir nesta opção, creio que você encontraria uma solução consultando o site do ASKTOM. Eu não tenho a solução de cabeça, mas talvez você tivesse que armazenar as informações em um objeto do tipo TYPE para burlar este erro (creio que você consegue localizar este exemplo no site do ASKTOM).

Agora, se para seu trabalho acadêmico você não precisa obrigatóriamente usar a trigger, eu te passaria uma "receita de bolo":

- Crie um indice UNIQUE para a coluna CD_RG;
- Crie um índice UNIQUE para a coluna CD_CPF;
- Crie um indice UNIQUE (e uma constraint PK) para a coluna ID_ALUNO;

Os passos acima vão garantir a integridade de dados da tabela. Se der algum pau informando registro repetido, então proceda com a limpeza dos registros duplicados.

Feito este passo inicial, então altere então a sua procedure:

- Em primeiro lugar, você vai fazer um

Selecionar tudo

UPDATE ALUNO SET ... WHERE CD_RG = ... AND CD_CPF = ...;

- Imediatamente após o comando de UPDATE, você vai colocar um IF do tipo

Selecionar tudo

        IF SQL%ROWCOUNT = 0 THEN 
            INSERT INTO ALUNO (...) VALUES (SEQ_ALUNO.NEXTVAL, ....) 
        END IF;

Unindo as linhas, o código fica algo parecido com isso:

Selecionar tudo

BEGIN
...
    UPDATE ALUNO 
          SET ... 
     WHERE CD_RG = ... 
          AND CD_CPF = ...;
    COMMIT;
        IF SQL%ROWCOUNT = 0 THEN 
            INSERT 
               INTO ALUNO (...) 
            VALUES (SEQ_ALUNO.NEXTVAL, ....) ;
            COMMIT;
        END IF;
...
END;

A variável "SQL%ROWCOUNT" é um recurso do ORACLE que indica quantos registros foram afetados por um comando de INSERT, UPDATE ou DELETE. Se o seu UPDATE não encontrou registros, então SQL%ROWCOUNT deve apresentar valor ZERO.

Você pode sofisticar esta lógica de UPDATE -- IF SQL%ROWCOUNT -- INSERT, substituindo tudo por um comando de MERGE.

O "MERGE" faz toda esta lógica que eu coloquei acima, com a vantagem de ser muito mais performático do que as minhas três instruções (UPDATE - SQL%ROWCOUNT - INSERT).

Não sei se você percebeu um ponto adicional, mas seria desnecessário fazer um

Selecionar tudo

SELECT SQ_ALUNO.NEXTVAL FROM DUAL

Basta colocar no seu código de INSERT o comando SQ_ALUNO.NEXTVAL como valor a ser populado para a coluna ID_ALUNO.

Abraços e tudo de bom !

Sergio Coutinho

[rodolpho.netto]

Eu li alguma sobre usar package e a trigger, se alguém puder me ajudar quanto a isso, eu preciso fazer dessa forma por questão de definição

[stcoutinho]

Rodolpho,

Se você precisa continuar com a sua trigger, você precisará criar alguns objetos adicionais - como tabelas temporárias ou packages - para armazenar o valor do registro. Talvez precise criar mais de uma trigger, que serão disparadas em diferentes eventos.

Veja se este link do ASKTOM te ajuda. Ele criou uma package para impedir o erro de MUTATING TABLE

http://asktom.oracle.com/pls/asktom/f?p ... 9621337269

Abraços,

Sergio Coutinho