guia de base de datos con trigger

Implementación de Trigger’s

GUIA PARA EL DESARROLLO DE LABORATORIO DE BASE DE DATOS

1. OBJETIVO

a. Conformar una Guía de Procesos para ser aplicado en el desarrollo del Proyecto de Base de datos,

2. CONTENIDO

La presente guía pretende describir paso a paso las acciones a llevar a cabo para el desarrollo de una propuesta orientada al uso de una base de datos en una empresa.

PASO 1: ELABORACION DEL MODELADO DE DATOS EN ERWIN

DESCRIPCION DEL CASO

CONTROL DE PRESTAMOS Y DEVOLUCIONES DE LIBROS EN UNA BIBLIOTECA

La Biblioteca es una institución creada con la finalidad de proveer de libros, manuales, revistas, CD, entre otros materiales de lectura, a los diversos usuarios Lectores de Chimbote para cuya tarea se ha definido el siguiente procedimiento:

Los Lectores son personas naturales identificados con una ficha de inscripción a través de su número de DNI, nombre completo, dirección, número de teléfono fijo, número de teléfono móvil, e-mail y un atributo denominado estado que tendrá los siguientes valores:

1. Lector castigado, 2. libro indefinidamente, 3. Lector amonestado hasta nuevo aviso, 4. Lector separado.

Algunos libros tienen más de un ejemplar disponible para el servicio de lectura. Asimismo, se llevará registro de los Autores. Tendremos en cuenta que un Autor puede haber escrito uno o más libros y un libro

puede estar escrito por uno o más autores. De igual manera se tendrá en cuenta la información de las Editoriales a las que

pertenecen los libros de la biblioteca. Los ejemplares de Libros pueden estar disponible para lectura en sala o a

domicilio. Asimismo, los ejemplares tienen registro de su estado actual; el mismo que puede

ser:o 1: “Buen estado”o 2: “Manchado”o 3: “Roto”o 4: “Perdido”

Además; se llevará registro de la disponibilidad del ejemplar del libro; es decir, si dicho ejemplar se encuentra en calidad de “prestado”, para ello utilizaremos un

1


atributo del Ejemplar que marcará 1 si el ejemplar está prestado ó marcará 2, si el ejemplar está disponible.

Cuando el ejemplar de un libro es entregado en calidad de “préstamo”, el atributo disponibilidad pasará a “prestado”; es decir, cambiará de 1 a 2. Cuando el ejemplar es devuelto por el lector, el atributo cambiará nuevamente a “disponible”; esto es, será modificado de 2 a 1.

La solicitud del Ejemplar de un Libro se lleva a cabo a través de la Ficha de Préstamo en la cuál se consignarán los siguientes datos: fecha de préstamo del Libro, DNI del Lector, Tipo de préstamo (1: Lectura en sala / 2. Lectura a domicilio), fecha a devolver el ejemplar, fecha de devolución (al ingresar el préstamo del ejemplar, estará en blanco y se cargará en el registro de devolución), Estado del Libro (Se cargará en el registro de devolución y será: 1. Buen estado, 2. Manchado, 3. Roto, 4. Perdido).

Cuando un nuevo préstamo se “inserte” en la tabla de préstamos haremos los siguientes controles:

o Verificar el estado del lector para saber si está autorizado.o Verificar el estado del Libro para saber si está disponibleo Actualizar el atributo disponibilidad del ejemplar, que pasará de disponible

a “prestado”. Cuando un ejemplar ha sido devuelto, “Actualizaremos” la tabla de préstamos y

llevaremos a cabo los siguientes controles:o Verificar que la fecha a devolver sea menor o igual a la fecha de devolución

del libro. Si no es asi, aplicar una sanción y actualizar el “estado” del lector teniendo en cuenta que su nuevo valor será: 3. “Lector amonestado hasta nuevo aviso”.

o Verificar el estado del Libro y compararlo con el estado del ejemplar; si no coinciden; aplicar una sanción, y actualizar el estado del Lector según sea el caso.

o Actualizar el atributo disponibilidad del ejemplar, que pasará de 1. Prestado a su nuevo valor 2. Disponible.

Elaborar el modelado de datos normalizado del caso descrito, teniendo en cuenta que habrá una tabla donde se registrará el movimiento de libros, la misma que operará de la siguiente manera:

a) Al Insertar un nuevo préstamo de Libro se disparará un Trigger que controle los casos descritos, en consecuencia; se desarrollará un trigger FOR Insert.

b) Al Actualizar la devolución de un Libro prestado, se disparará un Trigger que controle los casos descritos, en consecuencia; se desarrollará un trigger FOR Update.

El Modelado propuesto, es el siguiente:

1


PASO 2: IMPLEMENTACION DE UN TRIGGER EN ERWIN

TRIGGER PARA INSERCION EN LA TABLA MOVE_LIBROS(Registro de un nuevo Préstamo de Libro)

Crearemos el trigger desde ERwin llamado “InsertaPrestamos” sobre la tabla MOVE_LIBROS para la operación de INSERCION; de la siguiente manera:

Hagamos click derecho sobre la tabla MOVE_LIBROS y elijamos la opción trigger; asi:

1


A continuación veremos la ventana Triggers, aquí pulsemos el botón New… y veremos la ventana: New trigger; entonces escribiremos el nombre del Trigger, asi:

1


En la siguiente ventana elegiremos la opción Insert, y quedará asi:

Ahora, pasaremos a la pestaña: Code y borraremos el código hasta dejarlo como se indica a continuación:

1


Entonces escribiremos el siguiente código Transact SQL:

/* CREATE TRIGGER InsertaPrestamosON MOVE_LIBROSFOR INSERTAS */BEGIN SET NOCOUNT ON; DECLARE @estadoLector char(01) DECLARE @disponibilidad char(01)

select @estadolector = (select LEC_estado

from LECTORES L inner join inserted i on L.LEC_dni = i.LEC_dni) select @disponibilidad = (select EJE_disponibilidad from EJEMPLARES E inner join inserted i on E.EJE_numero = i.EJE_numero) if @disponibilidad = '1' AND @estadolector = '4' begin UPDATE EJEMPLARES SET EJE_disponibilidad = '2' FROM EJEMPLARES E inner join MOVE_LIBROS M on E.EJE_numero = M.EJE_numero UPDATE LECTORES

1


SET LEC_estado='3' from LECTORES L inner join MOVE_LIBROS M on L.LEC_dni = M.LEC_dni endELSE begin if @disponibilidad = 2 Begin RAISERROR('El Libro esta prestado', 10,1) ROLLBACK TRANSACTION End IF @estadolector = 1 Begin RAISERROR('El Lector tiene libro prestado fuera de fecha', 10,1) ROLLBACK TRANSACTION End IF @estadolector = 2 Begin RAISERROR('El Lector esta castigado', 10,1) ROLLBACK TRANSACTION End IF @estadolector = 3 Begin RAISERROR('El Lector tiene un libro prestado', 10,1) ROLLBACK TRANSACTION End end ENDGO

TRIGGER PARA ACTUALIZAR LA TABLA MOVE_LIBROS(Registro de la Devolución de Libros)

Seguiremos los pasos antes señalados en ERwin hasta ver la siguiente ventana:

1


Pasaremos a la pestaña Code y completaremos con el siguiente código Transact SQL:

/* CREATE TRIGGER devolucionPrestamos ON MOVE_LIBROS FOR UPDATEAS */ BEGIN SET NOCOUNT ON DECLARE @estadolibro char(01) DECLARE @fechaDevuelto datetime DECLARE @fechaDevolver datetime select @estadolibro = (select MOV_estadolibro from inserted) select @fechaDevuelto = (select MOV_estadolibro from inserted) select @fechaDevolver = (select MOV_estadolibro

1


from inserted) IF(@estadolibro = 1) BEGIN UPDATE EJEMPLARES SET EJE_disponibilidad = '1' FROM inserted I inner join EJEMPLARES E on E.LIB_codigo = I.LIB_codigo and E.EJE_numero = I.EJE_numero IF(@fechaDevuelto <= @fechaDevolver) BEGIN UPDATE LECTORES SET LEC_estado='1' -- Habilitado FROM inserted I inner join LECTORES L on I.LEC_dni = L.LEC_dni END ELSE BEGIN UPDATE LECTORES SET LEC_estado='3' -- Inhabilitado FROM inserted I inner join LECTORES L on I.LEC_dni = L.LEC_dni END

-- Actualiza el estado de los Ejemplares UPDATE EJEMPLARES SET EJE_estado=@estadoLibro FROM inserted I inner join EJEMPLARES E on E.LIB_codigo = I.LIB_codigo and E.EJE_numero = I.EJE_numero END ELSE --No está permitido devolver libros en mal estado BEGIN rollback transaction raiserror('Libro en mal estado', 10, 1) END ENDgo

PASO 3: IMPLEMENTACION DE LA BASE DE DATOS EN SQL SERVER 2005

A continuación, pasaremos el modelo de datos desarrollado en ERwin a la Base de datos en SQL Server, de la siguiente manera:

Ingresemos a ERwin y elijamos las siguientes opciones del Menú Principal:

Tools Forward Engineers/Schema generation ..

1


Veremos la siguiente ventana:

Apliquemos click en el botón Preview… y seleccionemos el código Transact SQL generado, luego copie al portapapeles dicho código, de la siguiente manera:

1


Ahora, ingresemos a Microsoft SQL Server 2005 donde crearemos una nueva base de datos con el nombre BIBLIOTECA, asi:

Seleccionemos la opción New Query del menú auxiliar y pegaremos el código Transact SQL del portapapeles, entonces la ventana quedará asi:

1


En este punto podremos pulsar F5 y se ejecutará el código Transact SQL.

PASO 4: IMPLEMENTACION DE UNA APLICACIÓN INTEGRADORA EN VISUAL BASIC NET 2005

Ingresemos a Visual Basic Net 2005Apliquemos en: Archivo Nuevo Proyecto Nombre del Proyecto: WinProyectoApliquemos en: Datos Agregar nuevo origen de datos …Veremos la siguiente ventana:

1


Apliquemos en el ícono Base de datos y hagamos click en el botón Siguiente >La Conexión de datos se refiere a la base de datos: Biblioteca, creada en SQL Server.A continuación, clickeamos en Siguiente >; para guardar la cadena de conexión.Veremos la siguiente ventana en la que elegiremos el check Tabla, asi:

1


Click en el botón Finalizar, entonces veremos la siguiente ventana:

Ahora, pasaremos la información de cada tabla de datos a cada formulario de acuerdo a uno de los formatos siguientes, haciendo click en cada tabla; veamos:

Despues de elegir DataGridView, Arrastraremos la tabla Autores al formulario y veremos:

1


Haremos lo mismo en un nuevo formulario, por cada una de las tablas y listo; ya podremos disponer de los datos en formularios de la aplicación.

Diseño del Formulario Principal

Agregaremos un nuevo formulario haciendo click derecho sobre WinBiblioteca del explorador de soluciones; asi:

1


En la siguiente ventana elegiremos Formulario primario MDI, asi:

1


Se mostrará el siguiente formulario en el que podremos diseñar el menú para acceder a cada formulario donde haremos el ingreso de los datos de cada tabla.

El menú quedará asi:

Para “enlazar” cada opción del menú con cada formulario, haremos doble click sobre la opción y escribiremos el nombre del formulario y el método show; por ejemplo:Form1.show

1

guia de base de datos con trigger

Documents