07. Mecanismos de actividad: disparadores

Concepto de disparador

La evolución en el tiempo de una base de datos se produce por la ejecución de operaciones de actualización realizadas por los usuarios (interactivamente) o por los programas (aplicaciones) que acceden a la base de datos. En muchas aplicaciones sin embargo, es conveniente que la base de datos evolucione, independientemente de la intervención del usuario, como respuesta a la ocurrencia de algún evento o porque se produce una situación.

En los sistemas de bases de datos clásicos, este comportamiento autónomo del sistema se modelaba en el código de los programas que accedían a la base de datos; en los modernos sistemas de bases de datos, este comportamiento autónomo se puede especificar en el esquema de la base de datos. Estos sistemas se conocen como sistemas de bases de datos activas o sistemas activos. El concepto de actividad no es exclusivo de los sistemas de bases de datos relaciones, pudiéndose incorporar a otros tipos de sistemas de bases de datos. En este texto nos referiremos exclusivamente al caso relacional.

Son muchos los ejemplos en los que esta capacidad de comportamiento autónomo por parte del sistema permite modelar mejor las reglas de funcionamiento interno de una organización.

Ejemplo 2.50

Considérese el área de deportes de una universidad, que tiene informatizado su sistema de información. La reserva de pistas deportivas, el control del uso de las pistas reservadas y la gestión de las sanciones impuestas a los usuarios que no utilizan las pistas reservadas, se hace automáticamente a través de terminales dispuestas a tal efecto.

El uso de un sistema de gestión de bases de datos activas, simplificaría la programación de la aplicación, ya que permitiría modelar en el esquema de la base de datos algunas de las reglas internas que rigen en el área de deportes. Sean las dos relaciones de la siguiente figura, parte del esquema de la base de datos. La relación Reservas, contiene información sobre la reserva de pistas por parte de los usuarios (pista reservada, fecha y hora de la reserva, dni del usuario, y la confirmación de su uso). La relación Sanción contiene información sobre las sanciones a los usuarios que no han confirmados una pista reservada (dni del usuario y fecha de la sanción).

Reservas                    Sanción

    pista                            dni

    fecha                           fecha

    hora

    dni

    confirmada

Gestión del uso de las pistas reservadas: cada usuario después de usar una pista reservada debe validar su uso, introduciendo sus datos y los de la pista desde una terminal.

Una regla de actividad definida en el esquema de la base de datos permitiría generar automáticamente las sanciones a aquellos usuarios que no hubiesen validado una pista reservada. El aspecto de esta regla de actividad sería el siguiente.

Gestión de reservas: un usuario sancionado no puede reservar pistas hasta que no venza su sanción.

Una regla de actividad definida en el esquema de la base de datos permitiría controlar automáticamente las reservas no autorizadas. El aspecto de esta regla de actividad sería el siguiente.

Gestión de sanciones: un usuario sancionado al que le vence la fecha de la sanción debe ser liberado de ésta.

Una regla de actividad definida en el esquema de la base de datos permitiría controlar automáticamente el levantamiento de sanciones. El aspecto de esta regla de actividad sería el siguiente.

Ejemplo 2.51

En el contexto de la base de datos del Ejemplo 2.35, considérese la siguiente regla de funcionamiento interno, “siempre que se dé de baja a un profesor sus datos deben grabarse en un histórico de profesores”. La operación que da de baja a un profesor es una operación DELETE sobre la relación Profesor, la operación que permite grabar sus datos en un histórico de profesores es una operación INSERT en la relación Histórico_Profesores. En un SGBD activo, la inserción automática de los datos del profesor, que ha sido dado de baja, en el histórico podría hacerla el sistema sin la intervención del usuario como respuesta a la operación de borrado.

Un sistema de gestión de bases de datos activo es aquel que ejecuta acciones como respuesta a la ocurrencia de un evento o una situación.

El comportamiento activo del sistema se modela en el esquema de la base de datos por medio de reglas de actividad o disparadores. Un disparador es una regla en la que se especifica una acción que el SGBD debe ejecutar como respuesta a la ocurrencia de un evento, siempre que la condición especificada se satisfaga. En los ejemplos, Ejemplo 2.50, y Ejemplo 2.51, se han representado gráficamente disparadores, para expresar reglas de funcionamiento de una organización, en estos disparadores se modelan acciones que el SGBD ejecutará autónomamente sin la intervención del usuario.

Reglas Evento-Condición-Acción

Como se ha ilustrado en el apartado anterior, el comportamiento activo del sistema se modela en el esquema de la base de datos por medio de reglas de actividad o disparadores que, independientemente de la sintaxis seguida en cada SGBD, tienen la siguiente estructura:

EVENTO-CONDICIÓN-ACCIÓN

En la componente EVENTO del disparador se especifica el suceso a cuya ocurrencia debe responder el sistema.

En la componente CONDICIÓN del disparador se especifica el contexto (condiciones) en el cual la regla cuyo evento se ha producido debe ser ejecutada.

En la componente ACCIÓN del disparador se especifican las acciones que deben ser ejecutadas por el sistema como respuesta a la ocurrencia del evento cuando la condición se cumple.

Ejemplo 2.52

En el Ejemplo 2.50, el evento, la condición y la acción de cada regla serían las siguientes:

Ejemplo 2.53

En el Ejemplo 2.51, el evento, la condición y la acción de la regla serían:

Un sistema activo debe ser capaz de realizar tres funciones diferenciadas para procesar los disparadores presentes en el esquema de la base de datos: activación, evaluación y ejecución. El sistema activa una regla cuando su evento ocurre. En algún instante de tiempo posterior a la activación, el sistema debe evaluar la condición de los disparadores activados y en algún instante posterior a la evaluación, el sistema debe ejecutar los disparadores activados cuya condición se ha evaluado a cierto.

Para poder usar un sistema activo, deben conocerse perfectamente dos elementos diferenciados del mecanismo de actividad: su sintaxis y su semántica. En la definición de la sintaxis se expresa lo que se puede incluir en cada componente de un disparador, y en la definición de la semántica de ejecución, se expresa cómo son procesados por el sistema, en tiempo de ejecución, los disparadores presentes en el esquema de la base de datos. Estos dos elementos pueden variar de un sistema a otro. A continuación se presenta un breve resumen de las opciones disponibles tanto en sintaxis como en semántica en los sistemas activos. Más adelante se concretarán estas opciones para el mecanismo de actividad propuesto en SQL3.

Sintaxis de los disparadores:

En la componente EVENTO pueden aparecer: operaciones de actualización de la base de datos, operaciones de consulta, eventos temporales, o eventos generados desde aplicaciones.

La componente CONDICIÓN consiste generalmente en una expresión de tipo lógico o en una llamada a una función de tipo lógico escrita en algún lenguaje de programación.

En la componente ACCIÓN pueden aparecer: operaciones de actualización de la base de datos, operaciones de consulta, sentencias de control de transacciones, sentencias de control de errores o llamadas a procedimientos escritos en algún lenguaje de programación.

Ejemplo 2.54

En el Ejemplo 2.50, en la regla de actividad "Gestión de las pistas reservadas" el evento es un evento temporal (24h. del día); en la regla "Gestión de reservas" el evento es una operación de actualización de la base de datos (INSERT INTO Reservas); en la regla "Gestión de sanciones" el evento es un evento temporal (24 h. del día).

En las tres reglas del Ejemplo 2.50, las condiciones son expresiones lógicas que podrían escribirse utilizando la sintaxis del SQL. La condición de la regla "Gestión del uso de las pistas reservadas" es "si existen pistas del día no confirmadas", esta condición podría escribirse en SQL como (SELECT COUNT(*) FROM Reservas WHERE NOT confirmada)>0. La condición de la regla "Gestión de reservas" es "si el usuario está sancionado", esta condición podría escribirse en SQL como (dni IN (SELECT dni FROM Sanción)). Por último la condición de la regla "Gestión de sanciones" es "si existen sanciones que vencen en el día" y podría escribirse en SQL como (SELECT COUNT(*) FROM Sanción WHERE SYSDATE-fecha >15)>0, asumiendo que la sanción dura 15 días.

En el Ejemplo 2.50, la acción de la regla "Gestión del uso de las pistas reservadas" consiste en una operación de actualización de la base de datos (INSERT INTO Sanción); la acción de la regla "Gestión de reservas" consiste en una operación de control de errores que genera un error y aborta la operación del usuario; por último la acción de la regla "Gestión de sanciones" consiste en una operación de actualización de la base de datos (DELETE FROM Sanción).

Ejemplo 2.55

En la regla del Ejemplo 2.51, el evento es una operación de actualización de la base de datos (DELETE FROM Profesor), no existe condición (CIERTO), y la acción es una operación de actualización de la base de datos (INSERT INTO Histórico_Profesores).

Semántica de ejecución (o modelo de ejecución):

La semántica de ejecución varía mucho de un sistema a otro. Cada sistema sigue un algoritmo de ejecución en el que se intercala la ejecución del evento, la comprobación de las restricciones de integridad relevantes para el evento, la ejecución de las acciones referenciales (ON DELETE, ON UPDATE) asociadas a las claves ajenas relevantes para el evento, y la ejecución de los disparadores activados por el evento. Si durante la ejecución de un disparador se produce un error, cualquier operación ya ejecutada es deshecha así como la operación en que consiste el evento que activó el disparador.

La diferencia en la semántica de ejecución de los distintos sistemas reside en el tratamiento que se realiza de los siguientes problemas: acoplo, granularidad, tipo de procesamiento y resolución de conflictos.

  • Acoplo: se refiere al acoplo temporal entre las tres componentes del disparador, el acoplo entre la ocurrencia del evento y la evaluación de la condición, y el acoplo entre la evaluación de la condición y la ejecución de la acción. Los valores posibles del acoplo son dos: inmediato y diferido. Un acoplo inmediato significa que las dos componentes son consideradas de forma inmediata (una a continuación de la otra), y un acoplo diferido significa que la consideración de las dos componentes está separada por un lapso de tiempo. En los sistema relacionales, el acoplo entre la evaluación de la condición y la ejecución de la acción es inmediato, es decir cuando se evalúa la condición, si ésta es cierta se ejecuta inmediatamente la acción. En el caso del acoplo entre la ocurrencia del evento y la evaluación-ejecución36 se pueden encontrar los dos valores: acoplo inmediato en el que la evaluación-ejecución se realiza cuando ocurre el evento, y acoplo diferido, en el que la evaluación-ejecución se difiere al final de la transacción a la que pertenece el evento que activó el disparador.

  • Granularidad: se refiere al tratamiento de las actualizaciones múltiples, es decir al caso en que el evento afecte a varias tuplas de una relación. Existen dos opciones posibles: granularidad orientada a la tupla y granularidad orientada al conjunto. Una granularidad orientada a la tupla, significa que en el caso de que el evento afecte a varias tuplas, el disparador se ejecutará una vez distinta para cada una de ellas. Una granularidad orientada al conjunto (o a la sentencia), significa que, independientemente del número de tuplas afectadas por el evento, el disparador se ejecutará una única vez.

  • Procesamiento: se refiere a la consideración de los disparadores activados durante la ejecución de otro disparador, es decir al caso frecuente en el que durante la ejecución de la acción de un disparador se activen otros disparadores. Existen dos opciones posibles: un procesamiento iterativo y un procesamiento recursivo. En el procesamiento iterativo, los disparadores activados como consecuencia de la ejecución de otro disparador pasan a formar parte del conjunto de disparadores activados que están siendo procesados por el algoritmo. En el procesamiento recursivo, cuando la ejecución de un disparador activa otros disparadores, se congela la ejecución del algoritmo que estaba procesando el disparador original y se inicia una nueva ejecución del mismo.

  • Conflictos: se refiere al orden en que son seleccionados los disparadores activados por un evento para su ejecución, en el caso de que estos sean varios. Existen distintas opciones: fecha de creación de los disparadores, selección aleatoria, orden especificado por el diseñador, etc.

El conocimiento del algoritmo de ejecución seguido por un SGBD es importante para un buen uso del mecanismo de actividad del sistema (disparadores), sin embargo un estudio detallado de estos algoritmos se escapa de los objetivos de este curso.

Ejemplo 2.56

En el Ejemplo 2.51, interesa que el disparador tenga una granularidad orientada a la tupla, ya que el evento podría consistir en un borrado múltiple de varias tuplas de Profesor; en este caso la granualridad orientada a la tupla aseguraría que los datos de cada uno de los profesores dados de baja son insertados en la relación Histórico_profesores.

Ejemplo 2.57

Para ilustrar la utilidad de la granularidad orientada al conjunto, supóngase la siguiente regla de funcionamiento en el ejemplo relativo a la docencia en la Universidad, Ejemplo 2.35: “Cuando se realiza una actualización en la relación Profesor se debe hacer un registro de seguridad en la relación Accesos, indicando el usuario que realizó la actualización y la fecha de la misma”

En el apartado siguiente se estudia la sintaxis y la semántica de los disparadores en SQL3.

Disparadores en SQL

La última versión del lenguaje SQL, publicada en 1999 y conocida como SQL3, incorpora el concepto de disparador.

La sintaxis para la definición de un disparador en el esquema de la base de datos es la siguiente:

CREATE TRIGGER nombre_disparador

    {BEFORE | AFTER }

    {INSERT | DELETE | UPDATE [OF atributo1, atributo2, ..., atributon]} ON nombre_relación

    [ REFERENCING {OLD [ROW ] [AS ] nombre_referencia

                  | NEW [ROW ] [AS ] nombre_referencia

                  | OLD_TABLE [AS ] nombre_referencia

                  | NEW_TABLE [AS ] nombre_referencia }]

    [FOR EACH {ROW | STATEMENT} ]

    [WHEN ( condición ) ]

    {sentencia_SQL | bloque SQL/PSM | CALL procedimiento_SQL}

Evento:

{INSERT | DELETE | UPDATE [OF atributo1, atributo2, ..., atributon]} ON nombre_relación

El evento de un disparador en SQL sólo puede ser una única operación de actualización sobre una relación básica de la base de datos.

El evento de un disparador está implícitamente parametrizado con dos tipos de parámetros: parámetros de tupla y parámetros de tabla. Estos parámetros pueden ser referidos tanto en la componente condición como en la componente acción del disparador.

Los parámetros de tabla son OLD TABLE y NEW TABLE, estos parámetros se inicializan con el conjunto de tuplas afectadas por el evento con su valor antes y después de la actualización respectivamente.

Los parámetros de tupla son OLD ROW y NEW ROW, estos parámetros se inicializan (durante la ejecución del disparador) con cada una de las tuplas afectadas por el evento con su valor antes y después de la actualización respectivamente.

Si el evento es una operación INSERT, sólo están definidos los parámetros NEW; si el evento es una operación DELETE, sólo están definidos los parámetros OLD, y si el evento es una operación UPDATE están definidos los dos tipos de parámetros.

Los parámetros que vayan a ser utilizados en la condición o en la acción del disparador deben ser renombrado con la cláusula opcional:

REFERENCING {OLD [ROW ] [AS ] nombre_referencia

            | NEW [ROW ] [AS ] nombre_referencia

            | OLD_TABLE [AS ] nombre_referencia

            | NEW_TABLE [AS ] nombre_referencia }

Condición:

La condición de un disparador en una expresión lógica escrita con la sintaxis del SQL.

Acción:

La acción de un disparador puede ser:

  • una sentencia simple: operación_SQL.

  • un bloque escrito en SQL/PSM38: BEGIN ATOMIC ... END

  • una llamada a un procedimiento escrito en el lenguaje SQL/PSM o en algún lenguaje de programación.

Granularidad:

La granularidad de un disparador se elige para cada disparador independientemente. Existen dos tipos de granularidad: granularidad orientada a la tupla y granularidad orientada al conjunto de tuplas. La propiedad de granularidad está relacionada con la forma en que son consideradas las tuplas afectadas por el evento, cuando éstas son varias (actualización múltiple).

La granularidad orientada a la tupla se elige con la opción FOR EACH ROW. Una granularidad orientada a la tupla significa que el disparador se ejecutará una vez distinta para cada tupla afectada por el evento. Con este tipo de disparadores se pueden usar los dos tipos de parámetros, de tupla y de tabla. En la ejecución del disparador para cada una de las tuplas afectadas, los parámetros OLD ROW y NEW ROW se inicializan con el valor de la tupla, mientras que los parámetros de tabla se inicializan una única vez para el evento y no cambian durante toda la ejecución del disparador.

La granularidad orientada al conjunto se elige con la opción FOR EACH STATMENT, y este es el valor por defecto. Una granularidad orientada al conjunto significa que el disparador se ejecutará una única vez independientemente del número de tuplas que han sido afectadas por el evento. Con este tipo de disparadores sólo se pueden usar los parámetros de tabla.

Punto de ejecución del disparador:

Cuando un disparador activado (porque se ha producido su evento), es seleccionado por el sistema para su ejecución, las funciones de evaluación de la condición y de ejecución de la acción se realizan de modo inmediato. La única opción disponible para el diseñador es poder elegir si el disparador se procesa antes (BEFORE) o después (AFTER) de la ejecución del evento. Estas opciones se eligen con la cláusula {BEFORE | AFTER } de la sintaxis.

El tipo de procesamiento seguido en SQL3 es recursivo y en caso de conflicto se seleccionan los disparadores por la fecha de creación.

Ejemplo 2.58

Sea la regla de funcionamiento interno del área de deportes (Ejemplo 2.50), “Gestión de reservas”. El disparador que rechazaría el intento de reservar una pista por parte de un usuario sancionado, se define en SQL3 de la siguiente forma:

CREATE TRIGGER Control_reservas

    AFTER INSERT ON Reservas

    REFERENCING NEW ROW AS nueva_reserva

    FOR EACH ROW

    WHEN nueva_reserva.dni IN (SELECT dni FROM Sanción)

    BEGIN ATOMIC

    SIGNAL Reserva_rechazada

END

En la definición se da nombre al disparador (Control_reservas), y se especifican sus tres componentes. En la componente EVENTO (AFTER INSERT ON Reservas), se indica que el evento que activará la regla será cualquier operación de inserción sobre la relación Reservas. La cláusula FOR EACH ROW indica que la granularidad es orientada a la tupla, es decir el disparador se ejecutará una vez para cada tupla insertada por la sentencia INSERT (en SQL las inserciones pueden ser múltiples). La cláusula REFERENCING NEW ROW AS nueva_reserva, permite usar el parámetro NEW ROW con el nombre nueva_reserva, este parámetro se inicializará con el valor de la tupla insertada en cada ejecución del disparador. La componente CONDICIÓN (WHEN nueva_reserva.dni IN (SELECT dni FROM Sanción) ), permite especificar las condiciones que se deben cumplir para que una vez activado el disparador éste se ejecute; en el ejemplo la condición comprueba si el usuario que solicita la reserva, valor del parámetro nueva_reserva, está sancionado. La componente ACCIÓN del disparador, especifica la acción que debe ser ejecutada en el caso de que la condición se evalúe a cierto, es decir que el usuario esté sancionado; en el ejemplo la acción consiste en rechazar la operación de inserción del usuario, esto se expresa con una instrucción de SQL/PSM cuya función es activar una excepción previamente definida (Reserva_rechazada) que abortará la operación de inserción del usuario.

Ejemplo 2.59

El disparador del ejemplo Ejemplo 2.51, se escribiría en SQL:

CREATE TRIGGER Baja_Profesor

    AFTER DELETE ON Profesor

    REFERENCING OLD ROW AS viejo

    FOR EACH ROW

    BEGIN ATOMIC

        INSERT INTO Historico_Profesores (codigo, nombre, teléfono, categoría, dep, fecha)

        VALUES (viejo.codigo, viejo.nombre, viejo.telefono, viejo.categoria, viejo.dep, CURENT_DATE)

END

En la definición se da nombre al disparador (Baja_Profesor), y se especifican sus tres componentes. En la componente EVENTO (AFTER DELETE ON Profesor), se indica que el evento que activará el disparador será cualquier operación de borrado sobre la relación Profesor. La cláusula FOR EACH ROW indica que la granularidad es orientada a la tupla, es decir el disparador se ejecutará una vez para cada tupla borrada por la sentencia DELETE (en SQL los borrados pueden ser múltiples). La cláusula REFERENCING OLD ROW AS viejo, permite usar el parámetro OLD ROW con el nombre viejo, este parámetro se inicializará con el valor de la tupla borrada en cada ejecución del disparador. La componente CONDICIÓN no existe, ya que el disparador debe ejecutarse siempre que se produzca el evento. La componente ACCIÓN del disparador, especifica la acción que debe ser ejecutada cuando un profesor es borrado; en el ejemplo la acción consiste en insertar en Histórico_profesores una nueva tupla con los datos del profesor borrado, datos que están disponibles en el parámetro viejo.

Ejemplo 2.60

El disparador del ejemplo Ejemplo 2.58, se escribiría en SQL:

CREATE TRIGGER Control_accesos1

    AFTER DELETE ON Profesor

    FOR EACH STATEMENT

    BEGIN ATOMIC

        INSERT INTO Accesos (usuario, fecha) VALUES (CURRENT_USER, CURRENT_TIMESTAMP)

END

CREATE TRIGGER Control_accesos2

    AFTER INSERT ON Profesor

    FOR EACH STATEMENT

    BEGIN ATOMIC

        INSERT INTO Accesos (usuario, fecha) VALUES (CURRENT_USER, CURRENT_TIMESTAMP)

END

CREATE TRIGGER Control_accesos3

    AFTER UPDATE ON Profesor

    FOR EACH STATEMENT

    BEGIN ATOMIC

        INSERT INTO Accesos (usuario, fecha) VALUES (CURRENT_USER, CURRENT_TIMESTAMP)

END

En este ejemplo hacen falta tres disparadores, ya que el registro de seguridad en Accesos debe hacerse cuando se produce cualquier operación de actualización en la relación Profesor, y SQL sólo admite una operación en el evento del disparador. El disparador tienen una granularidad orientada al conjunto, es decir el disparador se ejecuta una única vez independientemente del número de tuplas actualizadas por el evento. No existe condición, y la acción consiste en una inserción en la relación Accesos, insertando un número de registro (generado automáticamente por el sistema), el identificador del usuario y la fecha, estos dos últimos datos obtenidos por funciones predefinidas del sistema. En el disparador no hace falta usar los parámetros del evento, ya que la acción es independiente del contenido de la actualización que lo ha activado.

Aplicaciones de los disparadores

Son muchas las aplicaciones de los disparadores:

  • Comprobación de la integridad.

  • Restauración de la consistencia.

  • Mantenimiento de datos derivados.

  • Reglas de funcionamiento de la organización.

Comprobación de la integridad:

Muchos sistemas relacionales no contemplan de forma completa el SQL estándar, y generalmente no permiten la definición de restricciones de integridad generales. En este caso el mecanismo de actividad (disparadores) puede ser utilizado para superar estas limitaciones.

Ejemplo 2.61

Sea la siguiente restricción de integridad, definida en la base de datos del Ejemplo 2.35, “los profesores que imparten la asignatura de código EST1 deben ser del departamento de Estadística (EST)”.

En un SGBD que contemple el SQL estándar, se podría definir esta restricción en el esquema de la base de datos con una instrucción CREATE ASSERTION.

CREATE ASSERTION docencia_EST1

    CHECK (NOT EXISTS (SELECT * FROM Docencia JOIN Profesor ON (codigo=cod_pro)

    WHERE Profesor.cod_dep<>”EST” AND Docencia.cod_asg=”EST1”) )

Si el SGBD impone limitaciones en el uso de la cláusula CHECK, los disparadores pueden servir para comprobar restricciones de integridad.

Para diseñar los disparadores necesarios para el control de una restricción de integridad, hay que hacer un análisis de la restricción para determinar qué operaciones pueden violarla, estas operaciones serán los eventos de los disparadores necesarios. La condición del disparador será una forma simplificada de la restricción de integridad, instanciada con los valores de la tupla actualizada y la acción será una instrucción de control de errores que rechace la operación de actualización del usuario. El análisis de la restricción en este ejemplo sería el siguiente:

Los disparadores definidos en SQL3 que controlarían esta restricción de integridad serían los siguientes:

CREATE TRIGGER RI_docencia1_EST1

    AFTER INSERT ON Docencia

    REFERENCING NEW ROW AS nueva

    FOR EACH ROW

    WHEN nueva.cod_asg = ”EST1”

    BEGIN ATOMIC

        DECLARE X CHARACTER (4);

        SELECT P.dep INTO X FROM Profesor P

        WHERE P.codigo = :nueva.cod_pro;

        IF X <> “EST” THEN SIGNAL error_EST1 ENDIF;

END

CREATE TRIGGER RI_docencia2_EST1

    AFTER UPDATE OF cod_pro, cod_asg ON Docencia

    REFERENCING NEW ROW AS nueva

    FOR EACH ROW

    WHEN nueva.cod_asg=”EST1”

    BEGIN ATOMIC

        DECLARE X CHARACTER (4);

        SELECT P.dep INTO X FROM Profesor P

        WHERE P.codigo = :nueva.cod_pro;

        IF X <> “EST” THEN SIGNAL error_EST1 ENDIF;

END

CREATE TRIGGER RI_docencia3_EST1

    AFTER UPDATE OF dep ON Profesor

    REFERENCING OLD ROW AS viejo

    REFERENCING NEW ROW AS nuevo

    FOR EACH ROW

    WHEN viejo.dep = ‘EST’ AND nuevo.dep<>”EST”

    BEGIN ATOMIC

        DECLARE X INTEGER;

        SELECT COUNT(*) INTO X FROM Docencia D

        WHERE D.cod-pro = :nuevo.codigo AND D.cod_asg=“EST1”

        IF X <> 0 THEN SIGNAL error_EST1 ENDIF;

END

Mantenimiento de datos derivados:

En el apartado 2.6, se presentó el concepto de vista como el mecanismo en el Modelo Relacional para definir información derivada. Como se explicó, una vista es una relación derivada que no tiene existencia real, sus tuplas se calculan cuando el usuario solicita una consulta sobre la vista. Como es obvio, el cálculo de la extensión de la vista puede consumir mucho tiempo si la extensión de la vista es grande o si la regla de derivación es compleja, por este motivo en algunas bases de datos es interesante mantener información derivada almacenada explícitamente a pesar de la redundancia que esto significa. En estos casos, para evitar inconsistencias debido a la redundancia, es conveniente que la información derivada que está almacenada sea mantenida automáticamente por el SGBD consistentemente con la regla de derivación que la define, y no permitir en ningún caso que sea actualizada por los usuarios. En este contexto los disparadores pueden ser utilizados para el mantenimiento automático de esta información derivada que se ha decidido mantener almacenada explícitamente.

Ejemplo 2.62

En la base de datos del Ejemplo 2.35, una consulta frecuente consiste en solicitar los datos del profesor y entre ellos el total de créditos que imparte. Esta consulta se resolvería con la siguiente sentencia SQL:

SELECT PX.codigo, PX.nombre, SUM (DX.gteo*AX.teo + DX.gprac*AX.prac)

FROM Profesor PX, Docencia DX, Asignatura AX

WHERE PX.codigo=DX.cod_pro AND DX.cod_asg=AX.codigo

GROUP BY PX.codigo, PX.nombre

Si esta consulta se realiza con frecuencia, puede ser conveniente, extender el esquema de la relación Profesor, con un atributo creditos, cuyo valor sea en todo instante el total de créditos que imparte el profesor. Este atributo derivado creditos será actualizado por el SGBD, que deberá reflejar cualquier cambio en la docencia del profesor. Esta función de mantenimiento automático del atributo derivado puede definirse en forma de disparadores en el esquema de la base de datos. Para realizar el diseño de los disparadores necesarios, es preciso analizar la regla de derivación del atributo creditos y determinar qué operaciones de actualización del usuario pueden cambiar el valor de dicho atributo; estas operaciones serán los eventos de los disparadores. Así mismo habrá que analizar, en función de la actualización del usuario, cuál debe ser la acción de cada disparador para que se actualice adecuadamente el atributo derivado.

Ley de derivación del atributo créditos: “el valor del atributo créditos de Profesor es la suma de todos los créditos impartidos por el profesor”

Aplicar la ley de derivación del atributo créditos en Profesor y actualizar dicho atributo:

CREATE TRIGGER total_créditos1

    AFTER INSERT ON Docencia

    REFERENCING NEW ROW AS nuevo

    FOR EACH ROW

    BEGIN ATOMIC

        DECLARE vteo, vprac DECIMAL(1,1);

        SELECT teo, prac INTO vteo, vprac FROM Asignatura WHERE codigo=:nuevo.cod_asg;

        UPDATE Profesor SET créditos=creditos+(:nuevo.gteo*vteo + :nuevo.gprac*vprac)

        WHERE codigo=:nuevo.cod_pro;

END

CREATE TRIGGER total_créditos2

    AFTER DELETE ON Docencia

    REFERENCING OLD ROW AS viejo

    FOR EACH ROW

    BEGIN

        DECLARE vteo, vprac DECIMAL(1,1);

        BEGIN

        SELECT teo, prac INTO vteo, vprac FROM Asignatura WHERE codigo=:viejo.cod_asg;

        UPDATE Profesor SET creditos=creditos - (:viejo.gteo*vteo + :viejo.gprac*vprac)

        WHERE codigo=:viejo.cod_pro

        END

    END

CREATE TRIGGER total_créditos3

    AFTER UPDATE OF gteo, gprac ON Docencia

    REFERENCING OLD ROW AS viejo

    REFERENCING NEW ROW AS nuevo

    FOR EACH ROW

    WHEN (viejo.cod_pro = nuevo.cod_pro AND viejo.cod_asg = nuevo.cod_asg)

    BEGIN

        DECLARE vteo, vprac DECIMAL(1,1);

        SELECT teo, prac INTO vteo, vprac FROM Asignatura WHERE codigo=:viejo.cod_asg;

        UPDATE Profesor

        SET creditos = creditos - (:viejo.gteo*vteo + :viejo.gprac*vprac) + (:nuevo.gteo*vteo + :nuevo.gprac*vprac)

        WHERE codigo=:viejo.cod_pro;

END

CREATE TRIGGER total_créditos4

    AFTER UPDATE OF cod_prof, cod_asg ON Docencia

    REFERENCING OLD ROW AS viejo

    REFERENCING NEW ROW AS nuevo

    FOR EACH ROW

    BEGIN

        DECLARE nteo, nprac, vteo, vprac DECIMAL(1,1);

        SELECT teo, prac INTO vteo, vprac FROM Asignatura WHERE codigo = :viejo.cod_asg;

        SELECT teo, prac INTO nteo, nprac FROM Asignatura WHERE codigo = :nuevo.cod_asg;

        IF :nuevo.cod_prof = :viejo.cod_prof

        THEN

        UPDATE Profesor

        SET creditos=creditos - (:viejo.gteo*vteo + :viejo.gprac*vprac) + (:nuevo.gteo*nteo + :nuevo.gprac*nprac)

        WHERE codigo = :viejo.cod_pro;

        ELSE

        UPDATE Profesor

        SET creditos = creditos - (:viejo.gteo*vteo + :viejo.gprac*vprac)

        WHERE codigo = :viejo.cod_pro;

        UPDATE Profesor

        SET creditos = creditos + (:nuevo.gteo*nteo + :nuevo.gprac*nprac)

        WHERE codigo=:nuevo.cod_pro;

        ENDIF;

END

Comments