Ayuda con Trigger

[bogeyboy]

Buenas tardes a tod@s, tengo el siguiente código en un trigger en el cual pretengo comprobar antes de que inserte o update en una tabla de la bd un campo en concreto, pero no funciona puesto que el registro me lo inserta a pesar de la comprobación que le hago.

create or replace trigger comprueba_departamento
before update or insert on DEPARTAMENTO
for each row
declare
numero number;
resultado number;
begin
if (mod(:new.department_id, 10)!=0) then
dbms_output.put_line('Holita esto está mal');
else
insert into departamento values (:old.department_id,:new.department_name,:new.mana ger_id,:new.location_id);
dbms_output.put_line(:new.department_id);
end if;
end;

Alguien me podría ayudar con este problemilla???

[yong321]

You shouldn't create a trigger that has the same action in the trigger body as one of your trigger conditions. Here's what I mean:

SQL> create table t (x int);

Table created.

SQL> create or replace trigger t_t before insert on t for each row begin insert into t values (1); end;
2 /

Trigger created.

SQL> insert into t values (1);
insert into t values (1)
*
ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "YHUANG.T_T", line 1
ORA-04088: error during execution of trigger 'YHUANG.T_T'
ORA-06512: at "YHUANG.T_T", line 1
ORA-04088: error during execution of trigger 'YHUANG.T_T'
...

What's the error you got when you say "no funciona"?

Yong Huang

[bogeyboy]

I have no error, when i say "no funciona" i mean that i try to verify if department_id is divisible by 10, if it's no disible it shouldn't insert any row into "departamento" but it doesn't verify anything and always insert the record when i do

"insert into departamento values (111,'Merchandising',123,456)" for example when i write this instruction should appear the message "Holita esto esta mal" and it doesn't.

thanks for answering so fast

PD.: Por cierto por si alguna otra persona lee, soy español y lo hablo y lo escribo también, por si me quisieran responder

[yong321]

Iniciado por bogeyboy

I have no error, when i say "no funciona" i mean that i try to verify if department_id is divisible by 10, if it's no disible it shouldn't insert any row into "departamento" but it doesn't verify anything and always insert the record when i do

"insert into departamento values (111,'Merchandising',123,456)" for example when i write this instruction should appear the message "Holita esto esta mal" and it doesn't.

I see what you mean. You can't do what you want. The trigger doesn't work like that. What your trigger code says is simply that before an insert, check if the department ID is divisable by 10. If not, output that line *and then continue to do the insert*. If yes, do an *extra* insert and then continue to do the regular insert, and of course in this case, the insert in the trigger code fires the trigger recursively, and then recursively, and ... until you hit ORA-36.

Anyway, the first if-block is an additional PL/SQL block, not something in place of the regular action of the trigger. That's the key you need to understand.

Yong Huang

[huesos52]

Aunque el inglés es una herramienta indispensable en el mundo de la informatica, este foro es latino y el idioma que se usa es el español. Existen infinidad de foros en inglés que tratan estos temas.

saludos

[yong321]

Iniciado por huesos52

Aunque el inglés es una herramienta indispensable en el mundo de la informatica, este foro es latino y el idioma que se usa es el español. Existen infinidad de foros en inglés que tratan estos temas.

saludos

Perdón. Me acordaré de enviar mensajes en español. Puedo leer pero escribir mal.

[huesos52]

Don´t worry yong321.

bogey boy...

La validación que tratas de hacer, es mejor hacerla en una función y no en un trigger.

Un trigger se dispara ante una acción (En este caso before) pero igual se está ejecutando. En cambio en una función, puedes recibir los parámetros de entrada de la inserción y validar si se pueden o no insertar deacuerdo a la condición.

Por ejemplo.

Código SQL:

CREATE OR REPLACE FUNCTION insertar(campo1 IN NUMBER, campo2 IN NUMBER) RETURN NUMBER
AS
BEGIN
IF (MOD(campo1, 10)!=0) THEN
dbms_output.put_line('Error');
ELSE
INSERT INTO tabla VALUES (campo1,campo2);
dbms_output.put_line(campo1);
END IF;
RETURN 1;
END insertar;
/
 

algo así...

saludos

[bogeyboy]

Muchas gracias por la respuesta a los dos. No me había dado cuenta de lo del tema de la recursividad que tiene el trigger, de todas maneras sigo sin entender como es que no funciona la comprobación que hago

[huesos52]

Un disparador se utiliza para realizar una acción con base en otra acción.

Le estas diciendo que cuando inserte o actualice, compruebe si inserta o no inserta.
tiene sentido eso para ti?

En el ejemplo que te pone yong321 si miramos a detalle que es lo que está haciendo tenemos:
Si se inserta o se actualiza en la tabla departamento, por favotr inserte en la tabla departamento. En la acción interna del trigger se dispara nuevamente el trigger sobrecargando el sistema en un loop infinito.

En pocas palabras, le estas dando tareas al trigger que no le corresponden. La validación de datos se hace en funciones o desde la aplicación.

se entiende?

[yong321]

Tal vez usted puede utilizar instead-of trigger. Al principio, mire este ejemplo:

SQL> create table t (x int);

Table created.

SQL> create view v as select * from t;

View created.

--Elimina dbms_output en la producción, que es sólo para depurar

SQL> create or replace trigger t_v
2 instead of insert on v
3 for each row
4 begin
5 if :new.x > 10 then
6 dbms_output.put_line('Too big!');
7 else
8 insert into t values (:new.x);
9 dbms_output.put_line('Good!');
10 end if;
11 end;
12 /

Trigger created.

SQL> set serverout on
SQL> insert into v values (11);
Too big!

1 row created.

SQL> select * from t;

no rows selected

SQL> insert into v values (9);
Good!

1 row created.

SQL> select * from t;

X
------------
9

Ignora el primer mensaje "1 row created." Todo lo que necesitas es
rename DEPARTAMENTO to DEPARTAMENTO_table;
create view DEPARTAMENTO as select * from DEPARTAMENTO_table;
create trigger comprueba_departamento instead of insert on DEPARTAMENTO ...

Yong Huang

[bogeyboy]

Muchas gracias a todos, está todo comprendido perfectamente, y gracias nuevamente por estar ahí a todas esas personas que alguna vez han respondido alguna duda como la que yo acabo de tener, muchas gracias de corazón

[bogeyboy]

Dentro del trigger que les puese arriba anteriormente, después de haber hecho un trigger instead of en una vista previamente creada hago la siguiente comprobación:

if (mod(:new.department_id, 10)!=0) then
dbms_output.put_line('Holita esto está mal');
else
insert into departamento values (:old.department_id,:new.department_name,:new.mana ger_id,:new.location_id);

Si yo fuera a introducir un department_id = 111, por ejemplo, debería saltarme el mensaje de 'Holita, esto está mal', pues resulta que no funciona.

Alguien me puede ayudar con ello???

[yong321]

Iniciado por bogeyboy

Dentro del trigger que les puese arriba anteriormente,
Si yo fuera a introducir un department_id = 111, por ejemplo, debería saltarme el mensaje de 'Holita, esto está mal', pues resulta que no funciona.

Puede utilizar

raise_application_error(-20000, 'Holita, esto está mal');

El crédito al Stephane Faroult

http://www.freelists.org/post/oracle-l/Insteadof-trigger-spurious-message,1

Yong Huang