Problemas con triggers

[jamejilla]

Tengo el siguiente trigger:

CREATE TRIGGER controlExclusión
BEFORE INSERT ON escribe
FOR EACH ROW

DECLARE contador NUMBER := 0;

BEGIN

IF :new.id_modelo IS NOT NULL THEN

SELECT COUNT (id_modelo)
INTO contador
FROM revistas
WHERE id_modelo = :new.id_modelo;

IF contador = 1 THEN

RAISE_APPLICATION_ERROR (-20001, 'No puedes asignar una modelo para que escriba en la revista de la cual es directora');

END IF;
END IF;


Al ejecutar una inserción en la tabla escribe se muestra el mensaje de alerta pero además se muestran mensajes de error y no sé de qué pueden ser:


insert into escribe (id_modelo,id_revista) values (6,6)
*

ERROR en línea 1:
ORA-20001: No puedes asignar una modelo para que escriba en la revista de la cual es directora
ORA-06512: en "lalala.CONTROLEXCLUSIÓN", línea 14
ORA-04088: error durante la ejecución del disparador 'lalala.CONTROLEXCLUSIÓN'

A ver si alguien me ayuda. Gracias !!

[jc3000]

Lógico y normal.

Con el RAISE_APPLICATION_ERROR estás forzando un error, definido por ti, pero error. Lo que tienes que hacer es gestionar ese error con una excepción.

Me pillas en casa, mañana en el curro si tengo tiempo lo verifico, pero supongo que con un WHEN OTHERS te sería suficiente.

[jamejilla]

Y capturando la excepción como dices, ¿también se mostraría el mensaje que quiero enseñar?

Si tienes un momento a ver si me dices cómo hacer

[jc3000]

Si, claro, preguntas por el ¿ sqlerrm ? y lo displayas

[jamejilla]

Gracias por la respuesta

Lo cierto es que no tengo ni idea de esto, ¿alguien me podría poner el código para capturar la excepción y ejecutar el trigger sin problema?

[xf_corp]

Hola, echale un vistazo a esto:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#ADFNS012
http://psoug.org/reference/exception_handling.html (WHEN OTHERS THEN with SQLCODE and SQLERRM)

Tu excepción podrías manejarla así:

CREATE TRIGGER controlExclusión
BEFORE INSERT ON escribe
FOR EACH ROW
DECLARE contador NUMBER := 0;
Modelo_Directora EXCEPTION; --//declara la excepción...
BEGIN

IF :new.id_modelo IS NOT NULL THEN

SELECT COUNT (id_modelo)
INTO contador
FROM revistas
WHERE id_modelo = :new.id_modelo;

IF contador = 1 THEN
RAISE Modelo_Directora; --//dispara la excepción...


END IF;

END IF;
EXCEPTION
WHEN Modelo_Directora THEN
Raise_application_error (-20001, 'No puedes asignar una modelo para que escriba en la revista de la cual es directora');
END;
/

pero dado que lo que aparentemente requieres es más bien solo desplegar un mensaje de información, más que manipular una excepción, podrías manejarlo de alguna de las dos formas siguientes:


CREATE TRIGGER controlExclusión
BEFORE INSERT ON escribe
FOR EACH ROW
DECLARE contador NUMBER := 0;
Modelo_Directora EXCEPTION; --//declara la excepción...
BEGIN

IF :new.id_modelo IS NOT NULL THEN

SELECT COUNT (id_modelo)
INTO contador
FROM revistas
WHERE id_modelo = :new.id_modelo;

IF contador = 1 THEN
RAISE Modelo_Directora; --//dispara la excepción...


END IF;

END IF;
EXCEPTION
WHEN Modelo_Directora THEN
dbms_output.put_line('No puedes asignar una modelo para que escriba en la revista de la cual es directora');
END;
/

ó:

CREATE TRIGGER controlExclusión
BEFORE INSERT ON escribe
FOR EACH ROW
DECLARE contador NUMBER := 0;
BEGIN

IF :new.id_modelo IS NOT NULL THEN

SELECT COUNT (id_modelo)
INTO contador
FROM revistas
WHERE id_modelo = :new.id_modelo;

IF contador = 1 THEN
dbms_output.put_line('No puedes asignar una modelo para que escriba en la revista de la cual es directora');


END IF;

END IF;

END;
/

Verifica la info en las referencias para más detalle. Espero te sea útil .

Saludos.

[jamejilla]

Muchas gracias por contestar xf_corp pero he probado los tres casos y no me soluciona el problema.

1- Me pasa lo mismo que me pasaba: Impide que se inserte la tupla y muestra el mensaje, pero además muestra mensaje de error
2- No me muestra ningún mensaje y además no impide que se inserte la tupla
3- No me muestra ningún mensaje y además no impide que se interte la tupla

[xf_corp]

Hola de nuevo jamejilla,

Mira:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/exception_definition.htm:

An exception is a runtime error or warning condition, which can be predefined or user-defined.Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements.

Como menciona jc3000, el que veas el mensaje de error es lógico y normal.

La razón por la cual cuando lanzas la excepción se impide el insert de la tupla es la siguiente:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm

"If a predefined or user-defined error condition or exception is raised during the execution of a trigger body, then all effects of the trigger body, as well as the triggering statement, are rolled back (unless the error is trapped by an exception handler). Therefore, a trigger body can prevent the execution of the triggering statement by raising an exception. User-defined exceptions are commonly used in triggers that enforce complex security authorizations or integrity constraints."

"unless the error is trapped by an exception handler"


Basicamente estas definiendo un mensaje de error al crear tu excepcion, por lo que comentaba con anterioridad que si solo requerías desplegar el mensaje de error debieses emplear dbms_output, si estas usando sqlplus por ejemplo, utiliza SET SERVEROUTPUT ON para habilitar la salida.

Puedes encontrar más info aquí:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_output.htm

O en español:
http://es.wikipedia.org/wiki/PL/SQL

En tal caso si solo requieres el desplegar el mensaje y no deseas que la excepcion se dispare generando el error, puedes manipularla dentro del bloque EXCEPTION, manejar el trigger como AFTER y eliminar la tupla insertada.


CREATE TRIGGER controlExclusión
AFTER INSERT ON escribe
FOR EACH ROW
DECLARE contador NUMBER := 0;
Modelo_Directora EXCEPTION; --//declara la excepción...
BEGIN

IF :new.id_modelo IS NOT NULL THEN

SELECT COUNT (id_modelo)
INTO contador
FROM revistas
WHERE id_modelo = :new.id_modelo;

IF contador = 1 THEN
RAISE Modelo_Directora; --//dispara la excepción...


END IF;

END IF;
EXCEPTION
WHEN Modelo_Directora THEN
dbms_output.put_line('No puedes asignar una modelo para que escriba en la revista de la cual es directora');
--//sentencias para eliminar la tupla insertada validando que solo remuevas la tupla insertada...
delete from escribe where id_modelo=:new.id_modelo and campoUnico=:new.campoUnico;
END;
/

Sabes me da curiosidad, en base a tu select:

SELECT COUNT (id_modelo)
INTO contador
FROM revistas
WHERE id_modelo = :new.id_modelo;

Si la modelo se encuentra en la tabla revistas quiere decir que es directora?. Simple curiosidad.

Espero que los comentarios te sean útiles.

Saludos.

[jc3000]

Mmmmmmmmmm.

¿ Un delete sobre la misma tabla del trigger ?. O mucho me equivoco o te va a dar un error tipo " Tabla Mutante " .

Si te pasa eso, prueba a poner el delete en un BEGIN-END con un PRAGMA AUTONOMOUS TRANSACTION a ver si cuela, pero tengo mis dudas.

No lo pruebo yo porque hoy lunes porque tengo mucho tajo y voy a estar muy liado, pero creo que ya te hemos dado tema para pensar e investigar.

[xf_corp]

jajaja, lo estaba pasando por alto, tienes razón, buen día .

[xf_corp]

Hola, me ha dado curiosidad, sin emabargo se requiere algo mas que la transaccion autonoma, en este caso, podrías darle la vuelta usando un savepoint; lo marcas antes de realizar el insert:
SAVEPOINT start_transaction;

y posteriormente dentro del la transaccion autonoma das el rollback:

ROLLBACK TO start_transaction;

creo que es buen punto de partida, solo tienes que realizar algunas pruebas, y se cuidadoso con el manejo de las excepciones, lo que muestro es solo un ejemplo.


CREATE or replace TRIGGER controlExclusion
AFTER INSERT ON escribe
FOR EACH ROW
DECLARE contador NUMBER := 0;
Modelo_Directora EXCEPTION; --//declara la excepción...
BEGIN

IF :new.id_modelo IS NOT NULL THEN

SELECT COUNT (id_modelo)
INTO contador
FROM revistas
WHERE id_modelo = :new.id_modelo;

IF contador = 1 THEN
RAISE Modelo_Directora; --//dispara la excepción...


END IF;

END IF;
EXCEPTION
WHEN Modelo_Directora THEN
dbms_output.put_line('No puedes asignar una modelo para que escriba en la revista de la cual es directora');
--//sentencias para eliminar la tupla insertada validando que solo remuevas la tupla insertada...
undoInsert(:new.id_modelo,:new.campoUnico);
END;
/


create or replace procedure undoInsert(id number, unico number) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

delete from escribe where id_modelo=id and campoUnico=unico;
ROLLBACK TO start_transaction;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/


begin
SAVEPOINT start_transaction;

insert into escribe values (2, 2);
insert into escribe values (3, 3);


insert into escribe values (1, 1);
EXCEPTION
WHEN OTHERS THEN
NULL;
end;
/

Espero sea ùtil, ha sido interesante. .

Saludos.