Prctica 3. uso de DISPARADORES

Base de Datos AvanzadaPrctica 3. Uso de Disipadores

Contenido1. Objetivo32. Disparador Auditora32.1 Particularidades42.2 Ejemplo de Uso42. Disparador de seguridad.52.1 Cdigo53. Disipador de Seguridad II63.1 Cdigo64. Disparador de Sustitucin74.1 Cdigo75. Disipador de Sustitucin II85.1 Cdigo86. Disipador96.1 Cdigo9Bibliografa10

1. ObjetivoEl objetivo de esta prctica es que los alumnos se familiaricen con el uso de los disparadores (triggers) en el desarrollo de procedimientos de control de la integridad de la base de datos, alertas y construccin de bases de datos activas. El alumno deber incluir en el esquema de la base de datos la definicin de una serie de disparadores y desarrollar los procedimientos para que: a) se activen y b) no se activen.2. Disparador AuditoraMediante el presente trigger pretendemos guardar en una tabla auxiliar todas las modificaciones que se van realizando en todos los campos de la tabla 'Persona' de la base de datos. Adems, podemos saber qu modificaciones se han realizado, con qu usuario, en qu fecha y hora y qu valores haba antes de la modificacin y despus de la modificacin.En primer lugar modificamos la base de datos de la prctica anterior para alojar la tabla de Auditora (Auditoria_persona):Como se aprecia en la tabla se incluyen los mismos atributos para guardar el valor anterior (nombre ms sufijo _old) y el nuevo valor (nombre ms sufijo _new).Posteriormente, creamos el trigger "trigger_auditoria_persona" el cual realiza la labor indicada antes:CREATE TABLE Auditoria_Persona(id INT NOT NULL AUTO_INCREMENT,usuario VARCHAR(100) NOT NULL,fecha DATETIME NOT NULL,nombre_old VARCHAR(30) NOT NULL,apellidos_old VARCHAR(50) NOT NULL,fotografia_old LONGBLOB,email_old VARCHAR(30),tipo_persona_old ENUM('Alumno','Profesor'),nombre_new VARCHAR(30) NOT NULL,apellidos_new VARCHAR(50) NOT NULL,fotografia_new LONGBLOB,email_new VARCHAR(30),tipo_persona_new ENUM('Alumno','Profesor'),CONSTRAINT pk_perPRIMARY KEY (dni));

2.1 Particularidades 1. Usamos AFTER UPDATE para indicar que el trigger se ejecute cada vez que un usuario realice alguna modificacin en la tabla 'Persona'.2. El trigger insertar un registro en la tabla auxiliar Auditoria_persona, en dicho registro, el trigger guardar para cada campo el valor anterior y el nuevo valor. Para ello se usan las clusulas especiales "OLD.nombre_campo" (el trigger obtendr el valor anterior al cambio del campo) y "NEW.nombre_campo" (el trigger obtendr el valor nuevo).3. La funcin CURRENT_USER() obtiene y almacena en la taba el usuario actual del sistema.4. La funcin NOW() obtiene la fecha y hora en que el usuario realiza el cambio en la tabla.2.2 Ejemplo de UsoPara probar el trigger he ejecutado la siguiente consulta que modifica el atributo email de la tabla Persona:UPDATE `facultad`.`Persona` SET `email` = 'alberto.cruz.cordoba@gmail.com' WHERE `Persona`.`dni` =43968324;

Como resultado de la consulta se ha aadido una fila a la tabla "auditoria_persona"

Ilustracin 1. Tabla Auditora

2. Disparador de seguridad.Consiste en un disipador que impida realizar actualizaciones de la base de datos en base a algn criterio relacionado con la fecha, usuario, etc Para resolver este problema hemos creado un disparador que no permita modificar la tabla Persona el domingo.2.1 Cdigo

/*Trigger 2*//*DELIMITER $$*/CREATE TRIGGER ASIGNATURA_BEF_DELBEFORE UPDATE ON Persona FOR EACH ROWBEGINif (SELECT CONVERT(DAYNAME(CURDATE()), CHAR(50))) = 'Sunday'THENSIGNAL SQLSTATE '45000'SET MESSAGE_TEXT='Borrado no permitido';END IF;END;/*$$*/

En primer lugar hemos indicado que el trigger se active antes de realizar la modificacin (BEFORE UPDATE ON). A continuacin hemos comprobado la fecha actual del sistema mediante una consulta que devuelva el da actual (CURDATE()). Si el resultado de la funcin es igual a 'Sunday' se muestra el mensaje indicado en la implementacin.

3. Disipador de Seguridad IIEste disipador consite en impedir realizar modificaciones en el esquema de la base de datos en base a algn criterio relacionado con la fecha, usuario, etc.En este caso, hemos creado un disparador "PREVENT_DROP" que se ejecuta cuando se desea borrar una tabla:3.1 Cdigo

CREATE or REPLACE trigger PREVENT_DROPbefore drop on i12lopie.schemaBEGINIF ora_dict_obj_owner='ROOT'and ora_dict_obj_type='TABLE'THENRAISE_APPLICATION_ERROR(-20002,'Operacin no permitida');end if;end;/

En primer lugar indicamos que el trigger se active antes de borrar la tabla, e indicamos el esquema de la base de datos (facultad.schema) a modificar. A continuacin comprobamos que el propietario del objeto es root y que el tipo de objeto es 'TABLE' (esto lo realizamos gracias a la estructura "ora_dict_obj"), en tal caso, el sistema muestra por pantalla un error, indicando que la operacin no est permitida. Hemos de destacar que este trigger ha sido implementado en pl/sql, ya que la sintaxis de mysql no permite implementar triggers sobre una sola tabla. Para ello se hace uso del esquema de la base de datos implementado con el sgbd Oracle facilitado en prcticas anteriores.

4. Disparador de Sustitucin Consiste en un disipador que sustituye a una restriccin de dominio existente en la base de datos. Una de las restricciones de dominio en la base de datos "facultad" es que la nota final de cada alumno debe estar comprendida entre 0 y 10, por lo que creamos un trigger que se encargue de este cometido.4.1 Cdigo

/*Trigger 3: Modificacin esquema bd*//*DELIMITER $$*/create trigger PREVENT_DROPbefore drop on facultad.schema FOR EACH ROWbeginif ora_dict_obj_owner = 'root'and ora_dict_obj_type = 'TABLE'thenSIGNAL SQLSTATE '45000'SET MESSAGE_TEXT='Operacin no permitida';end if;end;/*$$*/

En primer lugar se indica que el trigger NOTA_BEFORE_INSERT se active antes de la insercin del elemento. A continuacin, se comprueba que el valor del atributo nota_final que se desea guardar en la base de datos no est entre 0 y 10, para as mostrar un error con los detalles del conflicto.

5. Disipador de Sustitucin II Consiste en un disipador que sustituya a alguna restriccin de integridad de referencia existente en la base de datos. En este caso, hemos decidido representar la integridad creando un disparador que asegure el valor del atributo id_asignatura, clave fornea de la relacin Evaluacin_Item y clave primaria de la tabla Asignatura.5.1 Cdigo

/*Trigger 5: Integridad base de datos*//*DELIMITER $$*/CREATE TRIGGER EVALUACION_ITEM_BEFORE_UPDATEBEFORE UPDATE ON Evaluacion_Item FOR EACH ROWBEGINIF ((select id_asignatura from Asignatura where id_asignatura=NEW.id_asignatura) is null)THENSIGNAL SQLSTATE '45000'SET MESSAGE_TEXT='Violacin de integridad';END IF;END;/*$$*/

El primer paso es indicar que el trigger EVALUACION_ITEM_BEFORE_UPDATE se active antes de la modificacin que se quiere realizar. A continuacin comprobamos mediante una consulta si el nuevo valor de id_asignatura se encuentra dentro de la tabla Asignatura, si el resultado de la consulta es NULL, quiere decir que no se encuentra la asignatura indicada, por lo que se mostrar un mensaje de error.

6. DisipadorConsiste en un disparador sobre alguna tabla cuya condicin se satisfaga en funcin de la extensin de alguna otra tabla. Esta situacin es parecida a la anterior, con la salvedad de que el atributo a modificar no es clave de ninguna de las tablas. Con el presente trigger buscamos que al crear una asignatura introduzcamos un atributo "titulacin" de la que existan grupos de alumnos.6.1 Cdigo

/*Trigger 6: Extensin tabla*//*DELIMETER $$*/CREATE TRIGGER ALUMNO_AFTER_INSERTBEFORE INSERT ON Asignatura FOR EACH ROWBEGINIF ((select titulacion from Grupo where titulacion=NEW.titulacion) is null)THENSIGNAL SQLSTATE '45000'SET MESSAGE_TEXT='No existe la titulacin';END IF;END;/*$$*/

En primer lugar en el trigger ALUMNO_AFTER_UPDATE indicamos que el disparador se active antes de realizar la insercin. A continuacin, comprobamos que el atributo titulacin de la tabla Asignatura que acabamos de introducir se encuentra en la tabla Grupo, si no es as, se muestra un mensaje de error indicando que la titulacin no se encuentra en el sistema.

Bibliografa[1]. Gabillaud, Jrome: Oracle 11g. Coleccin Recursos Informticos. Eni Ediciones 2010

9