Ejercicios con Triggers en SQL Server

Regresar al índice

Saludos compañeros internautas. 

Para poder realizar los ejercicios de esta publicación necesitarás descargar la Base de Datos Hospital, pero no la que compartimos en publicaciones anteriores, sino una nueva versión adaptada para ejecutar estos trigger. Lo único que se hizo de la versión anterior es quitarles las restricciones de clave tanto primarias como foráneas. 

Link de descarga : BASE DE DATOS HOSPITAL_BD

Sin más preámbulos iniciemos con los ejercicios.

1) Crear un Trigger que borre en cascada sobre la tabla relacionada cuando borremos una sala. Mostrar el registro borrado al ejecutar el Trigger.

CREATE TRIGGER BORRARSALA
ON SALA
FOR DELETE
AS
DELETE FROM PLANTILLA FROM SALA, DELETED
WHERE SALA.SALA_COD = DELETED.SALA_COD
SELECT * FROM DELETED

DELETE FROM SALA WHERE SALA_COD = 1

2) Crear un Trigger que se active cuando Actualicemos alguna sala del hospital, modificando sus tablas relacionadas. Mostrar el registro Actualizado. 

CREATE TRIGGER MODIFICARSALA
ON SALA
FOR UPDATE
AS
UPDATE PLANTILLA
SET SALA_COD = INSERTED.SALA_COD
FROM PLANTILLA, INSERTED, DELETED
WHERE PLANTILLA.SALA_COD = DELETED.SALA_COD
SELECT * FROM INSERTED

UPDATE SALA SET SALA_COD = 8 WHERE SALA_COD = 2

3) Crear un Trigger que se active al eliminar un registro en la tabla hospital y modifique las tablas correspondientes.

CREATE TRIGGER BORRARHOSPITAL ON HOSPITAL
FOR DELETE
AS
DELETE FROM PLANTILLA FROM PLANTILLA,DELETED WHERE
PLANTILLA.HOSPITAL_COD = DELETED.HOSPITAL_COD
DELETE FROM SALA FROM SALA,DELETED WHERE
SALA.HOSPITAL_COD = DELETED.HOSPITAL_COD
DELETE FROM DOCTOR FROM DOCTOR,DELETED WHERE
DOCTOR.HOSPITAL_COD = DELETED.HOSPITAL_COD

DELETE FROM HOSPITAL WHERE HOSPITAL_COD = 45

4) Crear un Trigger para controlar la inserción de empleados, cuando insertemos un empleado se copiarán datos sobre la inserción en una tabla llamada Control_BD. Los datos que se copiarán son el Número de empleado, El usuario que está realizando la operación, la fecha y el tipo de operación. 

CREATE TABLE Control_BD
(
EMP_NO INT NULL,
USUARIO VARCHAR(20) NULL,
FECHA DATETIME NULL,
OPERACION VARCHAR(15) NULL
)
GO
CREATE TRIGGER [DAR ALTA]
ON EMP
FOR INSERT
AS
INSERT INTO Control_BD (EMP_NO, USUARIO, FECHA, OPERACION)
SELECT INSERTED.EMP_NO, USER_NAME(), GETDATE(), 'INSERCION'
FROM INSERTED

INSERT INTO EMP(EMP_NO, APELLIDO, OFICIO, DIR, FECHA_ALT, SALARIO, COMISION, DEPT_NO)
VALUES(7455,'GANOZA','EMPLEADO',7902,'29/05/2015',15520,0,20)

5) Crear un Trigger que actue cuando se modifique la tabla hospital y sobre todas las tablas con las que esté relacionadas.

CREATE TRIGGER MODIFHOSPITAL ON HOSPITAL
FOR UPDATE
AS
UPDATE PLANTILLA
SET HOSPITAL_COD = INSERTED.HOSPITAL_COD
FROM PLANTILLA, INSERTED, DELETED
WHERE PLANTILLA.HOSPITAL_COD = DELETED.HOSPITAL_COD
UPDATE SALA
SET HOSPITAL_COD = INSERTED.HOSPITAL_COD
FROM PLANTILLA, INSERTED, DELETED
WHERE SALA.HOSPITAL_COD = DELETED.HOSPITAL_COD
UPDATE DOCTOR
SET HOSPITAL_COD = INSERTED.HOSPITAL_COD
FROM DOCTOR, INSERTED, DELETED
WHERE DOCTOR.HOSPITAL_COD = DELETED.HOSPITAL_COD

UPDATE HOSPITAL SET HOSPITAL_COD = 90 WHERE HOSPITAL_COD = 18

6) Crear un Trigger en la tabla plantilla. Cuando actualicemos la tabla plantilla, debemos comprobar que el hospital que actualizamos existe, si intentamos actualizar el código de hospital, no podremos hacerlo si no existe relación con algún código de hospital. Realizar el mismo Trigger para las tablas relacionadas con Hospital.

CREATE TRIGGER ACTUALIZARPLANTILLA ON PLANTILLA
FOR UPDATE
AS
DECLARE @HOSPITAL INT
SELECT @HOSPITAL = I.HOSPITAL_COD
FROM HOSPITAL AS H
INNER JOIN INSERTED AS I
ON H.HOSPITAL_COD = I.HOSPITAL_COD
IF (@HOSPITAL IS NULL)
BEGIN
PRINT 'NO EXISTE EL CODIGO DE HOSPITAL'
UPDATE PLANTILLA SET HOSPITAL_COD = D.HOSPITAL_COD
FROM PLANTILLA AS H
, INSERTED AS I
, DELETED AS D
WHERE H.HOSPITAL_COD = I.HOSPITAL_COD
END
ELSE
PRINT 'EXISTE EL CODIGO EN EL HOSPITAL'

UPDATE PLANTILLA SET HOSPITAL_COD = 140 WHERE EMPLEADO_NO = 1009

7) Modificar el Trigger del ejercicio 4, utilizando transacciones y control de errores, si la operación es correcta, mostrará un mensaje positivo, si la operación no es correcta mostrará el error y un mensaje que indique que no se ha llevado a cabo la operación.

ALTER TRIGGER [DAR ALTA]
ON EMP
FOR INSERT
AS
DECLARE @ERROR INT
BEGIN TRAN
INSERT INTO Control_BD (EMP_NO, USUARIO, FECHA, OPERACION)
SELECT INSERTED.EMP_NO, USER_NAME(), GETDATE(), 'INSERCION'
FROM INSERTED
SET @ERROR = @@ERROR
IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
PRINT 'EXISTE UN ERROR EN EL TRIGGER'
PRINT @@ERROR
END
ELSE
BEGIN
COMMIT TRAN
PRINT 'EMPLEADO INSERTADO CORRECTAMENTE'
END

INSERT INTO EMP(EMP_NO, APELLIDO, OFICIO, DIR, FECHA_ALT, SALARIO, COMISION, DEPT_NO)
VALUES(8741,'ZEGARRA','EMPLEADO',7902,'30/05/2015',15520,0,20)

8) Crear un trigger que guarde los datos en la tabla controltrigger cuando se realice la baja de un empleado.

CREATE TRIGGER [DAR BAJA]
ON EMP
FOR DELETE
AS
INSERT INTO Control_BD(EMP_NO,USUARIO,FECHA,OPERACION)
SELECT DELETED.EMP_NO,USER_NAME(),GETDATE(),'BAJA'
FROM DELETED

DELETE FROM EMP WHERE EMP_NO = 8741

9) Crear un Trigger que guarde los datos en la tabla ControlTrigger cuando se relice una modificación en un empleado. Guardar la hora de la actualización en un campo aparte en la tabla ControlTrigger. (Añadir un campo)

ALTER TABLE Control_BD ADD HORA VARCHAR(10)
GO
CREATE TRIGGER [MODIFICAREMP]
ON EMP
FOR UPDATE
AS
DECLARE @HORA VARCHAR(10)
SET @HORA = CONVERT(CHAR(2),DATEPART(HH, GETDATE())) + ':'
+ CONVERT(CHAR(2),DATEPART(MI,GETDATE()))
+ ':' + CONVERT(CHAR(2),DATEPART(SS,GETDATE()))
INSERT INTO Control_BD(EMP_NO,USUARIO,FECHA,OPERACION,HORA)
SELECT DELETED.EMP_NO,USER_NAME(),GETDATE(),'MODIFICACION',@HORA
FROM DELETED, INSERTED
WHERE DELETED.EMP_NO = INSERTED.EMP_NO

UPDATE EMP SET APELLIDO = 'ARROYO' WHERE EMP_NO = 7499

10) Borrar todos los Triggers creados después de haber sido probados.

DROP TRIGGER BORRARSALA
DROP TRIGGER MODIFICARSALA
DROP TRIGGER BORRARHOSPITAL
DROP TRIGGER [DAR ALTA]
DROP TRIGGER MODIFHOSPITAL
DROP TRIGGER ACTUALIZARPLANTILLA
DROP TRIGGER [DAR BAJA]
DROP TRIGGER [MODIFICAREMP]

Descargar Scripts SQL

Espero haber ayudado en algo. Hasta la próxima oportunidad!

Regresar al índice

Twittear