SQL ORACLE - SELECT inside a Trigger

Asked

Viewed 381 times

1

When I try to run a Rigger that uses this function gives me error saying that the table is changing.

Function:

CREATE OR REPLACE FUNCTION func_tipo_inst (pCodI t_instituicao.id%TYPE) RETURN INTEGER IS
   resultado t_instituicao.deEnsino%TYPE;
   resultadoSec INTEGER;
BEGIN

    SELECT deEnsino
    INTO resultado
    FROM t_instituicao
    WHERE id=pCodI;

    SELECT count(*)
    INTO resultadoSec
    FROM t_pessoa
    WHERE idinst=pCodI;

    IF UPPER(resultado)='S' THEN
        IF resultadoSec=0 THEN
            RETURN -2;
        ELSE
            RETURN 1;
        END IF;
    ELSE
        RETURN 0;
    END IF;


EXCEPTION
     WHEN NO_DATA_FOUND THEN
         RETURN -1;
END;
/

But if I just run the function this way it no longer gives me any kind of error:

CREATE OR REPLACE FUNCTION func_tipo_inst (pCodI t_instituicao.id%TYPE) RETURN INTEGER IS
   resultado t_instituicao.deEnsino%TYPE;
BEGIN
    SELECT deEnsino
    INTO resultado
    FROM t_instituicao
    WHERE id=pCodI;

    IF UPPER(resultado)='S' THEN
        RETURN 1;
    ELSE
        RETURN 0;
    END IF;


EXCEPTION
     WHEN NO_DATA_FOUND THEN
         RETURN -1;
END;
/

Note: The SELECT alone work well and the database has all the necessary tables.

The mistake is really in building this function, but I don’t understand where I went wrong.

  • If Trigger is in one of the two tables of Function it gives this error because it is using a mutating table. What needs to be done ?

  • @Gabriel, you’re still in trouble?

  • No, I have already solved it. Thank you

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.