Quiero acercarles un artículo que he preparado para este blog relacionado con los triggers. Tipos, utilidad, consideraciones generales....
Todo al estilo de este blog, sencillo y con ejemplos.
Espero que les sea útil, saludos.
DML Triggers
·
Son procedimientos almacenados que se ejecutan automáticamente ante un evento
DML (Update – Delete – Insert) que
afecta una tabla o vista.
·
Se los puede utilizar para, una vez
desencadenados:
-
Forzar reglas de negocio
-
Mantener la integridad de datos
-
Querear otras tablas
-
Ejecutar complejas instrucciones SQL
·
La integridad debiera ser forzada al nivel más
bajo por índices que formen parte de un Primary Key o Foreing Key contraints -
·
Los CHECK Constraints son otra alternativa
·
Para asegurar la Integridad Referencial nada
mejor que los Foreing Key
·
Los DML
TRIGGERS son especialmente utilizados cuando,con el simple uso de un constraint,
no se cubren las necesidades de una
aplicación
·
Consideraciones
Generales y Beneficios:
· * No se pueden invocar por si mismos, se disparan
automáticamente
· * No reciben ni retornan parámetros
· * A diferencia de los constraint “check” pueden
hacer referencia a otras tablas (por ejemplo se puede controlar una inserción
en una tabla de ventas si y solo si el valor de un campo stock de una tabla artículos
sea mayor a x cantidad)
· * Se pueden crear más de un trigger para un mismo
evento en una tabla, con lo cual se pueden controlar múltiples alternativas
sobre la misma tabla.
· * Permiten evaluar el estado de una tabla antes y
después de una modificación y tomar acciones acorde a la evaluación
· * Permiten customizar mensajes de error, algo que
los constraints en general no permiten.
·
Syntaxis:
CREATE TRIGGER
<Nombre del Trigger>
ON <Nombre de
la Tabla>
AFTER <INSERT,DELETE,UPDATE>
AS
BEGIN
SET NOCOUNT
ON;
-- Inserta aquí las instrucciones
END
·
Consideraciones
Adicionales:
-
Los triggers DML utilizan dos tablas especiales
denominadas Inserted y Deleted.
-
Son tablas creadas automáticamente por el SQL
con la misma estructura que la tabla sobre la cual está definido el trigger
-
La tabla Inserted
solo tiene datos en operaciones de Insert y Update
-
La tabla Deleted
sólo tiene datos en operaciones de Delete
y Update
-
En caso de un update las tablas Inserted y
Deleted tienen data al mismo tiempo.
-
No se pueden modificar los datos de estas tablas
·
Ejemplos:
1) Se
graba un histórico de stock cada vez que se modifica un artículo de la tabla “articulos”
-----------------------------------------------------------------
--
TRIGGER DML
--
Detalle: este trigger genera un histórico de stock cada vez
que se modifica la existencia de un
artículo --
-----------------------------------------------------------------
CREATE TRIGGER TR_ARTICULOS
ON ARTICULOS
AFTER UPDATE
AS
BEGIN
INSERT INTO HCO_STOCK
(IDARTICULO,
STOCK, FECHA)
SELECT IDARTICULO, STOCK, getdate()
FROM INSERTED
END
--- Con este evento UPDATE se
desencadena el Trigger
UPDATE ARTICULOS
SET STOCK = STOCK + 10
WHERE IDARTICULO = 1
2) Podemos hacer que el trigger del
ejemplo 1 se desencadene sólo si una columna es afectada
CREATE TRIGGER TR_ARTICULOS
ON ARTICULOS
AFTER UPDATE
AS
BEGIN
IF UPDATE (STOCK) -- sólo si actualiza STOCK
BEGIN
INSERT INTO HCO_STOCK
(IDARTICULO, STOCK, FECHA)
SELECT IDARTICULO, STOCK, getdate()
FROM INSERTED
END
END
3) Podemos
hacer que el trigger deshaga toda la
operación incluyendo un ROLLBACK
CREATE TRIGGER TR_ARTICULOS
ON ARTICULOS
AFTER UPDATE
AS
BEGIN
INSERT INTO HCO_ARTICULOS
(IDARTICULO, STOCK, FECHA)
SELECT IDARTOCULO, STOCK, getdate()
FROM INSERTED
ROLLBACK
END
4) Podemos
DESACTIVAR/ACTIVAR un Trigger o Todos lo
Triggers de la tabla
----------------------------------------------------------
-- Desactivar y Activar Todos los
Triggers de una Tabla --
---------------------------------------------------------
-- Desactiva todos los trigger de la
tabla ARTICULOS
ALTER TABLE
ARTICULOS DISABLE TRIGGER
ALL
GO
-- Activa todos los trigger de la tabla
ARTICULOS
ALTER TABLE
ARTICULOS ENABLE TRIGGER
ALL
----------------------------------------------------
-- Desactivar y Activar Un Trigger en
Particular --
---------------------------------------------------
-- Desactiva el trigger TR_STOCK
DISABLE TRIGGER TR_STOCK ON ARTICULOS
GO
-- Activa
el trigger TR_STOCK
ENABLE TRIGGER TR_STOCK ON ARTICULOS
GO
DDL Triggers
·
Son triggers especiales que se crean a nivel de base de datos y que disparan
en respuesta a eventos DML (Update –
Delete – Insert)
·
Suelen ser utilizados para ejecutar tareas
administrativas en una base de datos auditando y regulando cierta clase de
eventos.
·
Syntaxis:
CREATE TRIGGER <Nombre del Trigger>
ON DATABASE
FOR <DROP TABLE, ALTER TABLE>
AS
BEGIN
SET NOCOUNT ON;
-- Inserta aquí las instrucciones
END
·
Ejemplos:
1)
El siguiente trigger impide que sentencias DROP
TABLE y ALTER TABLE a nivel de Base de Datos
CREATE TRIGGER TR_Seguridad
ON DATABASE FOR DROP_TABLE,
ALTER_TABLE
AS
BEGIN
RAISERROR ('No está permitido borrar ni modificar tablas !' , 16, 1)
ROLLBACK TRANSACTION
END
