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 ?
– Motta
@Gabriel, you’re still in trouble?
– RXSD
No, I have already solved it. Thank you
– Gabriel