TRIGGERS
o
disparadores
BASE DE DATOS II
Lic. Rosa Irene López Loza
Introducción
Los triggers (o disparadores) son elementos
clave que pueden automatizar acciones y
mantener la integridad de los datos.
¿QUÉ ES UN TRIGGER?
Un trigger (disparador o desencadenador) es un
tipo de procedimiento almacenado que se
ejecuta cuando se cumple una determinada
condición al realizar una operación.
¿Para qué sirve un TRIGGER SQL?
o Es una de las funcionalidades más útiles de las que
se dispone para diseño y mantenimiento de bases
de datos.
o Se puede implementar ciertas características sin
necesidad de desarrollar programación en otros
lenguajes externos.
Ventajas
Provee una alternativa para la verificación de la
integridad en los datos.
Se puede utilizar como tareas programadas (Task
Schedule).
Útiles para auditar los cambios en las bases de datos
Desventajas
Pueden aumentar la sobrecarga del servidor de
base de datos.
Se ejecutan y son invisibles desde las aplicaciones
cliente, por lo tanto, es difícil averiguar qué
sucede en la capa de base de datos
Clases de Triggers en SQL
Trigger DML
Trigger DDL
Trigger DML
Se ejecutan cuando un usuario intenta modificar datos
mediante un evento de lenguaje de manipulación de
datos (DML).
Los eventos DML son instrucciones INSERT, UPDATE o
DELETE de una tabla o vista.
Trigger DML
TABLA
OPERACIONES
INSERTAR (INSERT)
ACTUALIZAR (UPDATE)
ELIMINAR (DELETE)
CARACTERÍSTICAS DE UN TRIGGER
✓ Se crean para conservar la integridad referencial y
la coherencia de los datos entre distintas tablas.
✓ Un trigger se asocia a un evento sobre una tabla.
✓ Si se crea un trigger para alguna acción de una
tabla, este se ejecuta de forma automática.
DIFERENCIA CON LOS PROCEDIMIENTOS
ALMACENADOS DEL SISTEMA
✓ No pueden ser invocados directamente; se ejecuta
automáticamente.
✓ No reciben, ni retornan parámetros.
✓ Son apropiados para mantener la integridad de los
datos, no para obtener resultados de consultas.
Tipos de TRIGGER DML
Insertar Actualizar Eliminar
(INSERT) (UPDATE) (DELETE)
INSTEAD OF FOR o AFTER
Tipos de TRIGGER DML
INSTEAD OF
FOR o AFTER
Especifica que se ejecuta el trigger
AFTER especifica que el en vez de la instrucción SQL, por lo
desencadenador solo se activa que se suplantan las acciones de las
cuando todas las operaciones instrucciones desencadenadoras.
especificadas en la instrucción SQL
se han ejecutado correctamente INSTEAD OF no se puede especificar
para los desencadenadores DDL.
AFTER es el valor predeterminado
cuando solo se especifica FOR. Solo se puede definir un trigger
INSTEAD OF por cada instrucción
Los desencadenadores AFTER no se INSERT, UPDATE o DELETE en tablas o
pueden definir en las vistas. vistas.
Sintaxis
CREATE TRIGGER [ esquema.]nombre_trigger
ON [ Tabla | Vista ]
FOR | AFTER | INSTEAD OF
[ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ]
AS
Sentencia SQL
Ejemplo AFTER/FOR:
Implementar un Trigger que permita
mostrar un mensaje cada vez que se
inserte un registro en la tabla
pacientes.
Solución
CREATE TRIGGER TR_mensaje
ON Pacientes
FOR insert
AS
PRINT '*** PACIENTE REGISTRADO ***'
go
Para que se ejecute el trigger creado:
INSERT INTO pacientes VALUES ( 'p0011’, 'Pepito’,
'Perez’, 'Rico’, 'Av. arce N° 123’, 2293847,
'12/06/1999’, 9283746, '1234567’, 'M’, 'O+’ )
Ejemplo INSTEAD OF:
1. Crear una vista que muestre todos los datos de los
pacientes y el nombre del médico que lo atendió.
2. Crear un Trigger que permita insertar un registro en la
tabla pacientes, cada vez se inserte un registro en la vista
creada en el punto 1.
3. Realizar una consulta que ejecute el trigger
Solución
1
create view PacAtendidoMed
as
SELECT p.*, m.nombre, m.apPaterno, m.apMaterno
FROM pacientes p INNER JOIN atenciones a ON
p.idPac = a.idPac INNER JOIN medicos m ON m.id =
a.id
Go
Solución
2
Create trigger tr_InsertaPac
on PacAtendidoMed
instead of insert
as
insert into pacientes select idPac, nombrePac, apPaternoPac,
apMaternoPac, direccionPac, telefonoPac, fechaNacPac,
ciPac, nroSeguroPaciente, sexo, tipoSangrePac from inserted
Go
Para que se ejecute el trigger creado:
insert into PacAtendidoMed values ('P0011', 'Pepito', 'Perez',
'Prado', 'Calle Beni 700', 2908000, '12/12/1990', 9090909, null, 'M',
'AB-', 'Marcela', 'Perez', 'Maceda')
Ejercicio
1. Modificar la vista creada para que muestre además los datos de
la tabla atenciones.
2. Modificar un Trigger para que, además, permita insertar un
registro en la tabla Atenciones, cada vez se inserte un registro en
la vista creada en el punto 1. Los datos que se deben agregar
son: AT018, Medicina General, Resfrio y antibióticos.
3. Realizar una consulta que ejecute el trigger
A. Disparador de Inserción
Cuando se inserta una nueva fila en una tabla, SQL
Server inserta los nuevos valores en la tabla INSERTED
el cual es una tabla del sistema. Está tabla toma la
misma estructura del cual se originó el TRIGGER, de
tal manera que se pueda verificar los datos y ante un
error podría revertirse los cambios.
A. Disparador de Inserción
Tabla temporal
TABLA
INSERTED
INSERTAR
Ejemplo:
Implementar un trigger que permita crear una
replica de los registros insertados en la tabla
Atenciones para dicho proceso se debe crear
una nueva tabla llamada AtencionesBAK con
las mismas columnas de la tabla Atenciones.
Solución
CREATE TRIGGER TR_replicaAtencion
on Atenciones
after insert
as
Begin
insert atencionesBAK select * from inserted
end
Consulta que hará que se ejecute el
trigger:
insert into atenciones values ('at018', 'p0009',
'M002', ’A05', '08/08/2023’, ‘Resfrío’,
‘Antibióticos', null)
Ejemplo
Crear un TRIGGER que inserte registros en una
tabla HISTORIAL (debe estar creada), que
guarde la fecha de inserción, descripción y el
usuario que lo registró, cada vez que se realice
una inserción en la tabla Internaciones.
Solución
CREATE TRIGGER TR_InsertarInternacion
ON internaciones
FOR INSERT
AS
insert into historial
values(getdate(), ‘Paciente Internado', system_user)
GO
Ejemplo
Modifique el TRIGGER creado en el ejemplo
anterior para que almacene también el
código de internación. (para esto también
debe modificar la tabla HISTORIAL y agregar
un campo para almacenar el código)
ALTER TRIGGER TR_InsertarInternacion
ON internaciones
FOR INSERT Agregar esta instrucción y ver
AS ¿qué ocurre?
set nocount on
declare @codInter varchar(10)
select @codInter = idInternacion from inserted
insert into historial values (getdate(), @codInter,
‘Paciente Internado', system_user)
GO
Ejercicio
1. Modificar el TRIGGER anterior para que cuando se realice la
internación, se actualice el estado de la cama.
2. Para probar el trigger modificado, realice el siguiente ejercicio:
Realizar la internación de la paciente “Betty Llanos Yana” en el
área de Maternidad y cuyo médico responsable es “Luis Mirta Luna”.
