Disparadores o
Triggers
Disparadores o Triggers
Un Disparador o Trigger es una rutina autónoma asociada con una tabla o vista que
automáticamente realiza una acción cuando una fila en la tabla o la vista se inserta
(INSERT), se actualiza (UPDATE), o borra (DELETE). Un Disparador nunca se llama
directamente, en cambio, cuando una aplicación o usuario intenta insertar, actualizar, o
anular una fila en una tabla, la acción definida en el disparador se ejecuta
automáticamente (se dispara).
Disparadores o Triggers
Aplicaciones Típicas de triggers
❑ Hacer modificaciones en cascada sobre tablas relacionadas
❑ Deshacer cambios que violan la integridad de los datos
❑ Forzar restricciones que son muy complejas para reglas y restricciones
❑ Mantener datos duplicados
❑ Mantener columnas con datos derivados
❑ Hacer ajustes de registros
Disparadores o Triggers
Las ventajas de usar los Disparadores son:
❑ La entrada en vigor automática de restricciones de los datos, hace que los usuarios
entren sólo valores válidos.
❑ El mantenimiento de la aplicación se reduce, los cambios a un disparador se refleja
automáticamente en todas las aplicaciones que tienen que ver con la tabla sin la
necesidad de recompilar.
❑ Logs automáticos de cambios a las tablas. Una aplicación puede guardar un registro
corriente de cambios, creando un disparador que se active siempre que una tabla se
modifique.
❑ La notificación automática de cambios a la Base de Datos con alertas de evento en
los disparadores.
Disparadores o Triggers
Definición de un Triggers
Un trigger se define asociado con una tabla para una o más sentencias de manipulación
de datos.
Un trigger se puede definir para:
❑ Insert,
❑ Update,
❑ Delete
❑ Cualquier combinación de ellos
Disparadores o Triggers
Activación de un Triggers
Cuando se modifica un dato en una tabla que tiene declarado un trigger para
esa sentencia, el trigger se “dispara”
• El trigger se dispara una vez, independientemente del número de filas afectadas.
Disparadores o Triggers
Triggers y Transacciones
Un trigger es parte de la transacción que causa el disparo
El trigger puede deshacer:
❑ Así mismo solamente
❑ Así mismo y la sentencia que causa el disparo
❑ La transacción total
Disparadores o Triggers
❑Los triggers pueden:
▪ Declarar variables locales
▪ Invocar procedimientos almacenados
❑Los triggers no pueden:
▪ Llamarse directamente
▪ Usar parámetros
▪ Definirse sobre tablas temporales o vistas
▪ Crear objetos permanentes de base de datos
❑Las operaciones con registro mínimo (como select
into) no disparan los triggers
Disparadores o Triggers
Diferentes Tipos de Triggers
❑ Disparadores de nivel de fila: se ejecutan una vez
para cada fila afectada por una instrucción DML. Los
disparadores de nivel de fila se crean utilizando la
cláusula for each row en el comando create trigger.
❑Disparadores de nivel de instrucción: se ejecutan una
vez para cada instrucción DML. Por ejemplo, si una
única instrucción INSERT inserta 500 filas en una tabla
un disparador de nivel de instrucción para dicha tabla
sólo se ejecutará una vez. Los disparadores de nivel de
instrucción son el tipo predeterminado que se crea con
el comando create trigger.
Disparadores o Triggers
Diferentes Tipos de Triggers
❑Disparadores After: puesto que los disparadores son
ejecutados por sucesos, puede establecerse que se
produzcan inmediatamente después (after) de dichos
sucesos.
❑Disparadores Instead Of: puede utilizar INSTEAD OF
para lo que tiene que hacer en lugar de realizar las
acciones que invoca el disparador. Por ejemplo, podría
usar un disparador INSTEAD OF en una vista para
gestionar las inserciones en una tabla o para actualizar
múltiples tablas que son parte de una vista.
Disparadores o Triggers
Diferentes Tipos de Triggers
❑ Disparadores de esquema: puede crear disparadores
sobre operaciones en el nivel de esquema tales como
create table, alter table, drop table, audit, rename,
truncate y revoke. Puede incluso crear disparadores
para impedir que los usuarios eliminen sus propias
tablas. En su mayor parte, los disparadores de nivel de
esquema proporcionan dos capacidades: impedir
operaciones DDL y proporcionar una seguridad
adicional que controle las operaciones DDL cuando
éstas se producen.
Disparadores o Triggers
Diferentes Tipos de Triggers
❑Disparadores en nivel de base de datos: puede crear
disparadores que se activen al producirse sucesos de
la base de datos, incluyendo errores, inicios de sesión,
conexiones y desconexiones. Puede utilizar este tipo
de disparador para automatizar el mantenimiento de la
base de datos o las acciones de auditoría.
Disparadores o Triggers
Sintaxis simplificada:
create trigger trigger_name
on table_name
for {insert | update | delete} [, {insert |
update | delete} ...]
as
sql_statements
Disparadores o Triggers
Borrado de Triggers
Sintaxis simplificada:
drop trigger trigger_name
Ejemplo:
drop trigger TB_Facultad
Disparadores o Triggers
Activado y Desactivado de Triggers
Activado Un Disparador:
Enable TRIGGER Trigger-Name ON Table_Name
Ejemplo:
Enable trigger TR_REGISTRAR_BORRADO_HORARIO on TB_Horario
Activar Todos los Disparadore de una Tabla
Ejemplo: ENABLE TRIGGER ALL ON Tb_Horario
Desactivado un Disparador:
DISABLE TRIGGER Trigger-Name ON Table_Name
Ejemplo:
Disable trigger TR_REGISTRAR_BORRADO_HORARIO on TB_Horario
Desactivar Todos los Disparadores en una Tabla
Disable trigger ALL on TB_Horario
PROBLEMAS PRÁCTICOS
Problemas Prácticos
/*
-------------------------------------------------------------------
Programando un Trigger de Seguridad
Problema N1: Disparador que se activa al intentar
borrar estructura de la base de datos, enviando un mensaje de advertencia
-------------------------------------------------------------------
*/
--Creación de la Tabla a Eliminar
CREATE TABLE TB_HORARIO
(
Cod_Horario int not null primary key,
Descripcion_horario varchar (50) not null,
)
GO
--Creación del Trigger
CREATE TRIGGER TR_SEGURIDAD_BD
ON DATABASE FOR DROP_TABLE, ALTER_TABLE
AS
BEGIN
RAISERROR ('No está permitido borrar ni modificar tablas!', 16, 1)
ROLLBACK TRANSACTION
END
GO
--Probando el Disparador
DROP TABLE TB_HORARIO
GO
Problemas Prácticos
/*
-----------------------------------------------------------
Programando Borrar un Disparador
Problema N2: Borrar un Disparador de la Base de Datos
-----------------------------------------------------------
*/
DROP TRIGGER TR_SEGURIDAD_BD ON DATABASE
GO
Problemas Prácticos
/*
-------------------------------------------------------------------
Problema N3: Programando Disparador para Informar una inserción de
nuevo Registro.
-------------------------------------------------------------------
*/
CREATE TRIGGER TR_Insertar_Nuevo_Registro_Facultad
ON TB_FACULTAD
FOR INSERT
AS
PRINT 'Se ha adicionado nuevo Registro en la Tabla Facultad.'
GO
--Probando el Trigger. Insertando nuevo registro.
INSERT INTO TB_FACULTAD (Cod_Facultad, Nombre_Facultad)
VALUES (115, 'Facultad de Ingeniería en Sistemas Computacionales')
GO
DROP TRIGGER TR_Insertar_Nuevo_Registro_Facultad
GO
Problemas Prácticos
/*
------------------------------------------------------------------
Problema N4: Programando Disparador para actualizaciones.
------------------------------------------------------------------
*/
CREATE TRIGGER TR_Actualizar_Registro_Facultad
ON TB_FACULTAD
FOR UPDATE
AS
PRINT 'Se ha realizado Actualizaciones en la Tabla Facultad.'
GO
--Realizando la actualización en la tabla Facultad. Probando el
Trigger
UPDATE TB_FACULTAD SET NOMBRE_FACULTAD = 'Nueva Facultad'
Where Cod_Facultad = 115
Problemas Prácticos
/*
------------------------------------------------------------------
Problema N5: Programando Trigger para Informar Eliminación
Registros en la
tabla TB_HORARIO
------------------------------------------------------------------
*/
CREATE TRIGGER TR_Delete_Registro_TB_HORARIO
ON TB_HORARIO
FOR DELETE
AS
PRINT 'Se han borrado registros de la Tabla Horario.'
GO
--Insertando nuevos registros a la Tabla TB_Horario
INSERT INTO TB_HORARIO (Cod_Horario, Descripcion_horario) VALUES
(1, '07:50 - 08:35')
--Borrando Registros de la Tabla Horario. Probando el Trigger
DELETE FROM TB_HORARIO
Problemas Prácticos
/*
------------------------------------------------------------------------
Problema N6: Programando un Trigger para aplicación de regla de negocio.
------------------------------------------------------------------------
*/
CREATE TRIGGER TR_TB_FACULTAD_NO_REGISTRO_LUNES
ON TB_FACULTAD
INSTEAD OF INSERT
AS
IF (datename(WEEKDAY,current_timestamp))=Lunes'
BEGIN
RAISERROR('NO SE PERMITEN ADICIONES el Viernes!!!',16,1)
RETURN
END
ELSE
BEGIN
SET NOCOUNT ON;
INSERT INTO TB_FACULTAD
SELECT Cod_Facultad, Nombre_Facultad
FROM inserted
END
GO
-- Insertando nuevos registros
INSERT INTO TB_FACULTAD (Cod_Facultad, Nombre_Facultad)
VALUES (121, 'Registro del Viernes')
Problemas Prácticos
/*
--------------------------------------------------------------------------
Problema N7: Programando un Disparador para guardar una bitacora de registros
borrados.
--------------------------------------------------------------------------
*/
-- Creando la tabla de almacenar los registros
CREATE TABLE TB_HORARIO
(
hora Datetime,
fila int
)
GO
CREATE TRIGGER TR_REGISTRAR_BORRADO_HORARIO
ON TB_HORARIO
FOR DELETE
AS
INSERT INTO TB_BORRADO_HORARIO
VALUES (getdate(), @@rowcount)
RETURN
GO
--Insertar Registros en la Tabla Horario
INSERT INTO TB_HORARIO (hora, fila) VALUES (1, '07:50 - 08:35')
INSERT INTO TB_HORARIO (hora, fila) VALUES (2, '08:40 - 09:25')
INSERT INTO TB_HORARIO (hora, fila) VALUES (3, '09:30 - 10:15')
INSERT INTO TB_HORARIO (hora, fila) VALUES (4, '10:20 - 11:05')
INSERT INTO TB_HORARIO (hora, fila) VALUES (5, '11:10 - 11:55')
INSERT INTO TB_HORARIO (hora, fila) VALUES (6, '11:55 - 12:45')
GO
--Consultando la Tabla de Registros Borrados de Horario.
SELECT * FROM TB_BORRADO_HORARIO
DELETE FROM TB_BORRADO_HORARIO
--Eliminando Registros de la Tabla Horario. Probando el Trigger.
DELETE FROM TB_HORARIO
--Consultando la Tabla de Registros Borrados de Horario.
SELECT * FROM TB_BORRADO_HORARIO
SELECT * FROM TB_HORARIO
Problemas Prácticos
/*
-------------------------------------------------------------------
Problema N8: Programando un Trigger para registrar inserción de Facultades.
-------------------------------------------------------------------
*/
CREATE TABLE DB_INSERCION_REGISTRO
(
IdRegistro INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Fecha_Hora DATETIME NOT NULL DEFAULT (GETDATE()),
Usuario VARCHAR(60) NOT NULL DEFAULT (USER_NAME()),
Accion VARCHAR(50) NOT NULL,
Descripcion VARCHAR(500) NOT NULL
)
GO
-- Creación de un desencadenador AFTER INSERT.
CREATE TRIGGER Tr_Insercion_Facultad
ON TB_FACULTAD
AFTER INSERT
AS
BEGIN
DECLARE @nombre_facultad VARCHAR(60)
SELECT @nombre_facultad = TB_FACULTAD.Nombre_Facultad
FROM INSERTED TB_FACULTAD
INSERT INTO DB_INSERCION_REGISTRO (Accion, Descripcion)
VALUES ('Inserción de datos',
'Se ha insertado un nuevo registro en la tabla facultad con el nombre: ' + @nombre_facultad)
END
GO
-- Insertando nuevos registros
INSERT INTO TB_FACULTAD (Cod_Facultad, Nombre_Facultad)
VALUES (123, 'Nuevo Registro de Facultad')
SELECT * FROM DB_INSERCION_REGISTRO
--Eliminando el Trigger
DROP TRIGGER Tr_Insercion_Facultad
GO
Problemas Prácticos
/*
-------------------------------------------------------------------
Escriba el codigo de un Disparador que permita una vez actualizado el nombre de la
carrera, actualizar todos los correos de los estudiantes afiliados a la carrera
actualizada. El Correo Electrónico debe quedar con el nuevo nombre de la carrera
más el servidor '@[Link]'.
Problema N9
-------------------------------------------------------------------
*/
-- Creación de un desencadenador AFTER INSERT.
DROP TRIGGER Tr_Modificacion_Correo_Estudiante
GO
CREATE TRIGGER Tr_Modificacion_Correo_Estudiante
ON TB_CARRERAS
AFTER INSERT, UPDATE
AS
BEGIN
DECLARE @Nombre_Carrera VARCHAR(60)
DECLARE @cod_carrera Int
SELECT @Nombre_Carrera = TB_CARRERAS.Nombre_Carrera
FROM INSERTED TB_CARRERAS
SELECT @cod_carrera = TB_CARRERAS.Cod_Carrera
FROM INSERTED TB_CARRERAS
--Continúa el problema en la siguiente diapositiva
Problemas Prácticos
BEGIN TRANSACTION
UPDATE TB_ESTUDIANTES SET
Correo_Estudiante = @Nombre_Carrera + '@[Link]'
WHERE Cod_Carrera = @cod_carrera
COMMIT
END
GO
SELECT * FROM TB_CARRERAS
-- Insertando nuevos registros
INSERT INTO TB_CARRERAS(Cod_Carrera, Nombre_Carrera) VALUES (119, 'Nueva Carrera')
-- Modificando Registros de la Tabla Carrera
UPDATE TB_CARRERAS SET Nombre_Carrera = 'Nueva FISC' WHERE Cod_Carrera = 1010
--Evaluando los cambios
SELECT * FROM TB_ESTUDIANTES
SELECT * FROM TB_CARRERAS
GO
