Ejemplos de Trigger Trigger Tenemos Tenemos una tabla de archivos (llamada archivos ), en la que uno de los campos es un MD5 sobre el contenido del archivo (campo llamado dmd5). En una tabla anexa (llamada cuán tos registros tenemos con el mismo MD5, para aux), necesitamos llevar la cuenta de cuántos esto tenemos dos campos dmd5 ! cont. "s#, cada ve$ que insertamos un registro en archivos debemos de actuali$ar el contador correspondiente en aux. De igual manera, cuando borramos un registro de archivos , deberemos de decrementar el contador correspondiente en aux ! en caso de que sea el %nico, eliminar el registro. &bviamente esto se puede hacer desde el mismo programa de actuali$aci'n, pero resulta, ! es este caso en particular lo que motiv' el empleo de triggers, triggers, que son varios programas los que actuali$an esta tabla, as# que mantener cada uno de ellos se vuelve un tanto cuanto engorroso. a meor soluci'n es emplear triggers. triggers. *abe se+alar que el eemplo que se muestra a continuaci'n unciona s'lo de la versi'n de -ostgre/ 0.5.1 en adelante, dado que emplea el Procedure el Procedure Language -2pg/ Language -2pg/ ! algunas particularidades integradas a partir de esa versi'n. 3ueno, el c'digo para actuali$ar los valores es el siguiente DROP FUNCTION inc_aux (); CRETE FUNCTION inc_aux () RETURN! OP"UE ! # DEC$RE m%r&c r&cord; 'EIN !E$ECT INTO m%r&c FRO* aux +,ERE aux-dmd5 . NE+-dmd5; IF NOT FOUND T,EN IN!ERT INTO aux /$UE! (NE+-dmd50 1); E$!E UPDTE aux !ET cont.cont21 +,ERE dmd5 . NE+-dmd5; END IF; RETURN NE+; END; # $NUE #343s64#; DROP TRIER ins_arc ON archivos; CRETE TRIER ins_arc 'EFORE IN!ERT ON archivos FOR EC, RO+ E7ECUTE PROCEDURE inc_aux();
os DROPs antes de crear la unci'n ! el trigger , son para garanti$ar que las unciones no existen previamente ! sobre todo, porque tuve que hacer un demonial de pruebas antes de que la cosa alara. 4ecordemos que los triggers no triggers no pueden recibir argumentos ! siempre ! siempre tienen tienen que regresar un valor opaco. opaco. El s&4&ct se hace para averiguar si existe un registro con el MD5 ! en caso de no existir lo insertamos con el contador en uno ! en caso de que !a exista, incrementamos el contador
en uno. Mu! simple. 'lo es de notar que tuvimos que declarar un registro para hacer la consulta ! saber si el registro existe en la tabla anexa para actuali$arlo (u3dat&) o si será necesario crear uno nuevo (ins&rt). El registro NE+ se reiere al registro con que el que u disparado el trigger . Eliminamos el trigger para, en caso de que !a exista uno con el mismo nombre, garanti$ar que se eecutará el que vamos a declarar. *omo podemos ver, lostriggers están asociados a tablas ! por eso se debe de indicar de que tabla lo eliminamos al momento de eectuar el dro3. "hora veamos el caso en que se eliminan registros. -or supuesto, en este caso estamos eliminando un registro que está en la base, as# que no necesitamos ver primero si existe. El %nico considerando a tomar en cuenta es el caso en que el MD5 es %nico, en cu!o caso habremos de eliminar de la tabla dmd5 el registro. DROP FUNCTION d&c_aux (); CRETE FUNCTION d&c_aux () RETURN! OP"UE ! # 'EIN UPDTE aux !ET cont.cont81 +,ERE dmd5 . O$D-dmd5; DE$ETE FRO* aux 9h&r& cont : 1; RETURN NU$$; END; # $NUE #343s64#; DROP TRIER d&4_arc ON archivos; CRETE TRIER d&4_arc FTER DE$ETE ON archivos FOR EC, RO+ E7ECUTE PROCEDURE d&c_aux();
6na manera de probarlo, es la siguiente mancha. !E$ECT FRO* aux +,ERE dmd5.#1<=>5?@AB1<=>5?@AB1<=>5?@AB1<#; dmd5cont 888828888 ( ro9s) mancha. IN!ERT INTO archivos /$UE! (#'ORR*E#0##01<=>50#=181<8 1BBB#0#<=5B@#0 #1<=>5?@AB1<=>5?@AB1<=>5?@AB1<#0#1<=>5?@AB1<=>5?@AB1<=>5?@AB1<#0#Ca s&ro4a#); IN!ERT 1BA1@=1 1 mancha. !E$ECT FRO* aux +,ERE dmd5.#1<=>5?@AB1<=>5?@AB1<=>5?@AB1<#; dmd5cont 8888888888888888888888888888888828888 1<=>5?@AB1<=>5?@AB1<=>5?@AB1< 1 (1 ro9) mancha. d&4&t& Grom archivos 9h&r& arc.#'ORR*E#; DE$ETE 1 mancha. !E$ECT FRO* aux +,ERE dmd5.#1<=>5?@AB1<=>5?@AB1<=>5?@AB1<#; dmd5cont 888828888 ( ro9s)
"hora supongamos que tenemos un peque+o sistema de n'mina ! en el m'dulo de "3*77.7 queremos tener la garant#a m#nima de que no se insertará un registro sin nombre o con
salario negativo. "demás, queremos llevar nota de quin ! cuando modiic' los registros. ea la tabla CRETE T'$E &m3 ( nomHr& t&xt0 sa4ario int>0 4ast_dat& dat&tim&0 4ast_us&r nam&);
Deinimos la unci'n que 88estampe99 los cambios CRETE FUNCTION &m3_stam3 () RETURN! OP"UE ! # 'EIN 88 /&riGica 6u& &4 nomHr& % &4 sa4ario s& ha44an dado IF NE+-nomHr& I!NU$$ T,EN RI!E E7CEPTION ##nomHr& no 3u&d& s&r NU$$##; END IF; IF NE+-sa4ario I!NU$$ T,EN RI!E E7CEPTION ## no 3u&d& t&n&r un sa4ario NU$$##0 NE+-nomHr&; END IF; 88 "u& no t&na sa4ario n&ativo (3u&d& s&r c&ro) IF NE+-sa4ario : T,EN RI!E E7CEPTION ## no 3u&d& t&n&r un sa4ario n&ativo##0 NE+-nomHr&; END IF; 88 hora &stam3amos 6uiJn % cuKndo hiLo 4os camHios NE+-4ast_dat& . ##no9##; NE+-4ast_us&r . &t3us&rnam&(); RETURN NE+; END; # $NUE #343s64#;
*reamos el trigger CRETE TRIER &m3_stam3 'EFORE IN!ERT OR UPDTE ON &m3 FOR EC, RO+ E7ECUTE PROCEDURE &m3_stam3();
: vemos un eemplo mancha. ins&rt into &m3 va4u&s (#*&ia Pintita#0 <5); IN!ERT 11?=51@1 1 mancha. ins&rt into &m3 va4u&s (#*isha N&rita#0 <5); IN!ERT 11?=51@< 1 mancha. s&4&ct Grom &m3; nomHr& sa4ario4ast_dat& 4ast_us&r 888888888888828888888288888888888888888888888888882888888888 'i44 C4inton 1*on = Man 1<
ro9s) mancha. ins&rt into &m3 va4u&s (#P&rico C&ri44o#0 81); ERROR P&rico C&ri44o no 3u&d& t&n&r un sa4ario n&ativo
mancha. ins&rt into &m3 va4u&s (NU$$ 0 1); ERROR nomHr& no 3u&d& s&r NU$$ mancha. ins&rt into &m3 va4u&s (#P&rico C&ri44o#0 NU$$); ERROR P&rico C&ri44o no 3u&d& t&n&r un sa4ario NU$$
in embargo, se nos olvid' hacer una veriicaci'n básica mancha. ins&rt into &m3 va4u&s (##0 1); IN!ERT 11?=51@= 1 mancha. s&4&ct Grom &m3; nomHr& sa4ario4ast_dat& 4ast_us&r 888888888888828888888288888888888888888888888888882888888888 'i44 C4inton 1*on = Man 1<
/ueda como eercicio veriicar que no se inserten nombres en blanco.
CONCEPTO Y EJEMPLO DE USO Y CREACIÓN DE DISPARADORES (TRIGGERS EN ORACLE CONCEPTO 6n disparador deine una acci'n que la base de datos debe llevar a cabo cuando se produce alg%n suceso relacionado con la misma. os disparadores (triggers) pueden utili$arse para completar la integridad reerencial, tambin para imponer reglas de negocio compleas o para auditar cambios en los datos. El c'digo contenido en un disparador, denominado cuerpo del disparador , está ormado por bloques -2/. a eecuci'n de disparadores es transparente al usuario. -ara crear un disparador (trigger) en una tabla, el usuario con el que accedamos a &racle deberá ser propietario de la misma, teniendo as# el privilegio "TE4 para la tabla ' "TE4 ";: T"3E. "demás, dicho usuario, debe disponer del privilegio *4E"TE T4<==E4. Existen varios tipos de disparadores, dependiendo del tipo de transacci'n de disparo ! el nivel en el que se eecuta el disparador (trigger) 7.> Disp!r!dores de "i#el de $il! se eecutan una ve$ para cada ila aectada por una instrucci'n DM. os disparadores de nivel de ila se crean utili$ando la cláusula $or e!%& ro' en el comando %re!e rigger.
?.> Disp!r!dores de "i#el de i"sr)%%i*" se eecutan una ve$ para cada intrucci'n DM. -or eemplo, si una %nica intrucci'n <;E4T inserta 5@@ ilas en una tabla un disparador de nivel de instrucci'n para dicha tabla s'lo se eecutará una ve$. os disparadores de nivel de instrucci'n son el tipo predeterminado que se crea con el comando %re!e rigger. 1.> Disp!r!dores +e$ore , A$er puesto que los disparadores son eecutados por sucesos, puede establecerse que se produ$can inmediatamente antes (beore) o despus (ater) de dichos sucesos. A.> Disp!r!dores I"se!d O$ puede utili$ar <;TE"D &B para indicar a &racle lo que tiene que hacer en lugar de reali$ar las acciones que invoca el disparador. -or eemplo, podr#a usar un disparador <;TE"D &B en una vista para gestionar las inserciones en una tabla o para actuali$ar m%ltiples tablas que son parte de una vista. 5.> Disp!r!dores de es-)em! puede crear disparadores sobre operaciones en el nivel de esquema tales como create table, alter table, drop table, audit, rename, truncate ! revoCe. -uede incluso crear disparadores para impedir que los usuarios eliminen sus propias tablas. En su ma!or parte, los disparadores de nivel de esquema proporcionan dos capacidades impedir operaciones DD ! proporcionar una seguridad adicional que controle las operaciones DD cuando star se producen. 0.> Disp!r!dores e" "i#el de .!se de d!os puede crear disparadores que se activen al producirse sucesos de la base de datos, inclu!endo errores, inicios de sesi'n, conexiones ! desconexiones. -uede utili$ar este tipo de disparador para automati$ar el mantenimiento de la base de datos o las acciones de auditor#a.
EJEMPLO DE CREACIÓN DE TRIGGER (DISPARADOR DE NI/EL DE 0ILA 7.> En primer lugar abriremos la *onsola de &racle Enterprise Manager
seleccionaremos
elecionaremos la 3ase de Datos del árbol e introduciremos un usuario ! contrase+a co n permisos suicientes para crear trigger en la tabla de eemplo (en nuestro caso accederemos como s!stem)
?.> En el árbol accederemos a Esquema ! a continuaci'n seleccionaremos el usuario propietario de la tabla en la que crearemos el trigger, en nuestro caso "&;&. Despus pulsaremos en Tablas, seleccioremos la tabla a la que queramos aplicarle el disparador !, sobre la carpeta Disparadores pulsaremos con el bot'n derecho del rat'n, nos aparecerá un men% emergente, pulsaremos *rear...
1.> En ;ombre introduciremos el nombre del disparador ! en *uerpo del Disparador introduciremos el c'digo -2/ que queramos que se eecute, en nuestro caso queremos que el disparador a+ada un registro otra tabla llamada resultadodisparador cuando el valor que introdu$ca el usuario en el campo *"M-&? de la tabla -46E3" (despus de insertar una nueva ila) sea superior a 7@@@
En la pesta+a Evento de la ventana de *rear Disparador marcaremos Disparador en Tabla, marcaremos Despus ! marcaremos tambin en "rrancar Disparador
-ulsaremos en *rear !, si no ha habido errores el el c'digo -2/ nos aparecerá un mensae como este Disparador se ha creado correctamente
;ota tambin se pueden crear disparadores sin necesidad de utili$ar la intera$ gráica (*onsola de &racle Enterprise Manager). e pueden crear mediante /. a consulta / que crea el disparador anterior es la siguiente *4E"TE &4 4E-"*E T4<==E4 "&;&.<;E4*<&;;6ME4&M":&47@@@ "BTE4 <;E4T &; "&;&.-46E3" B&4 E"* 4&F FE; (neG.*"M-&? H 7@@@) 3E=<; insert into resultadodisparador (echa, aviso, tabla) values
(!sdate, 94egistro con *"M-&? superior a 7@@@9, 9-46E3"9)I E;DI *on este disparador, cuando un usuario inserte un registro en la tabla -46E3" cu!o *"M-&? tenga un valor superior a 7@@@ se insertará automáticamente (transparente para el usuario) otro registro en la tabla resultadodisparador con la echa de la inserci'n (s!sdate), el aviso 4egistro con *"M-&? superior a 7@@@ ! el nombre de la tabla origen del disparador -46E3". &bviamente, para que este disparador uncione correctamente deberán existir las tablas -46E3" (origen del disparador) ! resultadodisparador (donde se insertará el registro si se cumple la condici'n *"M-&? H 7@@@. a consulta / necesaria para crear la tabla -46E3" *4E"TE T"3E "&;&.-46E3" ( *"M-&7 J"4*"4?(7@) ;&T ;6, *"M-&? ;6M3E4) a consulta / necesaria para crear la tabla resultadodisparador *4E"TE T"3E "&;&.4E6T"D&D<-"4"D&4 (BE*" D"TE ;&T ;6, "J<& J"4*"4?(7@@) ;&T ;6, T"3" J"4*"4?(5@) ;&T ;6) En nuestro eemplo estamos trabaando en todo momento utili$ando el usuario "&;& para la creaci'n del disparador ! de las tablas, puesto que es el propietario de dichos obetos.
Posi.les errores1 i tras crear el disparador aparece con un icono roo con una K en medio es porque el c'digo -2/ no es correcto
En ocasiones &racle introduce alg%n E;D ! I de más al inal. -ara solucionarlo es suiciente con quitar los E;D ! I sobrantes ! volver a compilar ! guardar el disparador hasta que quede con el siguiente icono
-ara comprobar que el disparador unciona adecuadamente, reali$amos una inserci'n en la tabla -46E3" insert into alonso.prueba values (9-46E3" 79, 7@) con esta inserci'n el disparador no se eecutará pues *"M-&? es inerior a 7@@@, para comprobarlo hacemos un EE*T a la tabla 4E6T"D&D<-"4"D&4 (no nos deber#a devolver ning%n registro) select L rom alonso.resultadodisparador "hora reali$amos una inserci'n en la tabla -46E3" con el valor del *"M-&? superior a 7@@@ insert into alonso.prueba values (9-46E3" ?9, 75@@) "hora comprobamos con un EE*T que la tabla resultadodisparador tiene una nueva ila select L rom alonso.resultadodisparador
ALGUNAS CONSULTAS S2L PARA MODI0ICAR EL ESTADO DE UN TRIGGER -ara eliminar un trigger mediante / drop trigger nombretrigger -ara deshabilitar temporalmente un trigger (deará de reali$ar su unci'n) alter trigger nombretrigger disable -ara habilitar un trigger deshabilitado alter trigger nombretrigger enable -ara deshabilitar todos los triggers asociados a una tabla alter table nombretabla disable all triggers -ara habilitar todos los triggers asociados a una tabla alter table nombretabla enable all triggers
