Aquí la primera parte.
Obtención de información de los triggers
Los triggers igual que el
resto de objetos de la base de datos también aparecen en la tabla sys.sysobjets,
con el identificador TR en la columna xtype, por lo que
Si deseamos saber cuántos
triggers hay en una base de datos, basta hacer una consulta filtrando con TR en
la columna xtype. Esto mismo es válido para el resto de objetos como
procedimientos almacenados, tablas, vista, etc sólo que en el caso de los
procedimientos en vez de TR hay que filtrar por P.
Para obtener la
definición de los triggers definidos podemos consultar el campo text de la tabla sys.syscomments
donde viene la definición de todos los objetos de la base de datos. En el caso
de que no queremos que la definicion, del trigger sea consultada, también
podemos añadir la cláusula WITH ENCRIPTION en la creación del trigger. También
podemos obtener información del trigger ejecutando el procedimiento almacenado sp_help 'tr_nombre_Trigger'
Validar actualizaciones de tablas
Las principales utilidades de
los triggers consisten en controlar que las operaciones de actualización que se
llevan a cabo sobre las tablas sean coherentes pues centralizan las operaciones de validación en la base de datos al realizar
los procesos de añadir o eliminar datos.
Las tablas Inserted y Deleted
En multitud de ocasiones las
operaciones inserción y borrado de filas se Ilevan a cabo en cadena. En este tipo
de situaciones el trigger que responde a estas acciones puede necesitar valorar
qué cambios se han producido sobre la tabla, de manera que se pueda actuar en
consecuencia. Por tanto es necesario disponer de algún modo de información del
estado de la tabla antes y después de las modificaciones. SQL Server proporciona
dos tablas temporales denominadas inserted y deleted.
La tabla inserted almacena una
copia de las filas que se han añadido durante la ejecución de una sentencia
INSERT o UPDATE sobre una tabla que tiene asociado un trigger.
La tabla deleted almacena una
copia de las filas eliminadas durante una sentencia DELETE o UPDATE.
Evidentemente, una vez realizada la operación de borrado, las filas desaparecerán
de la tabla asociada al trigger y solo
aparecerán en la tabla deleted.
Creamos una tabla de ejemplo:
CREATE TABLE
tbTablaPruebas (
idClave TINYINT
, nombre VARCHAR(30)
, fecha DATETIME
)
|
Crearemos un trigger en el que podemos
especificar las tablas virtuales
INSERTED y DELETED, donde indicaremos que se dispare después
de un INSERT, UPDATE o DELETE y que haga una consulta a las tablas lógicas
antes mencionadas para ver su contenido.
CREATE TRIGGER
dbo.trPruebasDelIns ON
tbTablaPruebas
AFTER INSERT, UPDATE, DELETE
AS
SELECT * FROM deleted;
SELECT * FROM inserted;
|
Insertamos algunos datos en la tabla:
INSERT INTO
tbTablaPruebas
VALUES( 1,
'PEDRO' , '11/13/2015' )
INSERT INTO
tbTablaPruebas
VALUES( 2 ,
'PEPE', '11/13/2015' )
|
Al hacer una select se muestra automáticamente
una select de la tabla deleted (que aparece vacía) y el de la tabla inserted
que aparece con los datos insertados.
Ahora modificaremos una fila:
UPDATE tbTablaPruebas
SET nombre = 'LUIS' , fecha = '12/12/2015'
WHERE idClave = 2
El resultado es:
No existe una tabla virtual UPDATED,
el funcionamiento es parecido o idéntico a eliminar y después insertar.
Ahora realizamos un DELETE:
DELETE FROM
tbTablaPruebas
WHERE idClave = 1
Si necesitamos de los valores antes
que sufran algún cambio o si necesitamos llevar un registro de cambios, esto
nos puede ser de gran ayuda.
Triggers y transacciones
Los triggers pueden descartar
las transacciones que ejecutan. Es decir, los triggers pueden ser ejecutados
dentro de una transacción. Para ello, antes de comenzar a realizar cualquier
operación debemos incluir la sentencia BEGIN y justo debajo de la lista de
operaciones a realizar END de este modo si se produce una condición mientras se
ejecuta la acción que lea una sentencia ROLLBACK, o bien se produce un error de
base de datos, entonces se descartan todas
las operaciones realizadas en la transacción en curso. Por ejemplo en una operación
UPDATE asociada a un trigger si, por cualquier circunstancia falla una actualización
de una tabla, para evitar que la base de datos quede inconsistente deshacen
todas las operaciones realizadas desde la sentencia BEGIN.
CREATE TRIGGER
tr_nombre_Trigger
ON dbo.tbTabla
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE tbTabla set
campo1 = 'texto'
WHERE campo2 =
10
INSERT INTO tbTabla2 VALUES ('1','texto')
END
RAISERROR ('Notificación', 16,
10);
ROLLBACK;
GO
En este ejemplo si por ejemplo
falla el INSERT inferior, se descarta el UPDATE del principio. También podemos descartar la transacción o
ejecutarla explícitamente con la sentencia COMMIT para ejecutarla y ROLLBACK
para descartarla.
CREATE TRIGGER dbo.TrTrigger1 ON dbo.tbTabla1
AFTER INSERT
AS
IF EXISTS (SELECT *
FROM dbo.TbTabla2 p
JOIN inserted AS i
ON p.Campo1 =
i.Campo1
JOIN dbo.tbTabla2 AS v
ON v.Campo2 = p.Campo2
WHERE v.Campo3 = 'Valor'
)
BEGIN
UPDATE dbo.Tabla1 SET Campo1 = 4 WHERE dbo.Tabla1.Campo2 = 'Valor2'
UPDATE dbo.Tabla2 SET Campo1 = 3 WHERE dbo.Tabla2.Campo2 = 'Valor3'
COMMIT
--Da por validas todas las sentencias
ejecutadas desde el BEGIN
RAISERROR ('Descripción error de
usuario', 16,
1)
ROLLBACK;
--Descarta todas las sentencias
ejecutadas desde el BEGIN
RETURN
END;
GO
Transacciones y batches
Aparte de las transacciones
son definidas por el usuario, también se considera como una transacción las
sentencias incluidas en un batch, es decir en una secuencia de sentencias que
se envían al gestor conjuntamente. En este caso, todo el batch, es considerado
una única transacción. Si se ejecuta un trigger conteniendo la sentencia
ROLLBACK las sentencias siguientes no se ejecutarán pues el trigger descartará
la transacción por entero y por tanto, el contenido íntegro del batch.
Triggers
e Inserción condicional
Los triggers toman la
sentencia de modificación como un conjunto completo que debe ser aceptado o
descartado, es decir como una transacción. Por lo que no es posible aceptar
parte de la secuencia de operaciones de modificación y descartar el resto. Una
manera de saltarse este mecanismo validación es construir en el interior del
trigger una subconsulta que examine las filas modificadas una a una.
Si deseamos insertar un
registro en la tabla tbVentas cada vez que se realice una venta de un libro en
una librería, pero para ello es necesario que el identificador del título del
libro esté dado de alta en el almacén (la tabla tbTitulos) si no es asi,
elimina la venta automáticamente.
CREATE TRIGGER TR_InsertCondicional
ON dbo.tbVentas
AFTER INSERT AS
IF
(SELECT COUNT(*) FROM dbo.TbTitulos, inserted
WHERE dbo.TbTitulos.Id_titulo = inserted.Id_titulo) <> @@ROWCOUNT
BEGIN
DELETE dbo.tbVentas FROM dbo.tbVentas, inserted
WHERE dbo.tbVentas.Id_titulo = inserted.Id_titulo AND
inserted.Id_titulo NOT IN
(SELECT Id_titulo
FROM dbo.TbTitulos)
PRINT 'Sólo ejecuta la venta
si existe el título pedido.'
END
Como puede verse, en este
trigger se eliminan las filas que no cumplen la condición enunciada, y sólo
éstas. Como vemos, para ello se hace uso de la tabla inserted, descrita en el
apartado anterior. Cada proceso de inserción de filas en la tabla de ventas (que
podría ser de múltiples filas), los registros insertados se encuentran en la tabla
inserted, de manera que el trigger puede examinarlos y borrar los que no
coincidan con los títulos del almacén. De este modo los triggers sirven para
mantener la integridad de datos en una base de datos.
Ejemplo
completo de uso de triggers y procedimientos almacenados
Para definir los procedimientos
almacenados la base de datos estará disponible al público para que pueda
consultar por su cuenta, por ejemplo, los libros de un determinado autor. La
manera de hacer posibles estas consultas será mediante una pequeña aplicación cliente que enviará a
SQL Server las peticiones de datos. Parece evidente que la consulta de libros
de un autor será muy frecuente, de manera que será adecuado definir en la base
de datos un procedimiento almacenado que reciba como argumento el nombre del
autor del que se desea conocer su obra completa.
Este procedimiento se
almacenará en SQL Server y su ejecución será solicitada por las aplicaciones
cliente, obteniendo un rendimiento mejor del que se obtendría con la realización
de la consulta. Una posible implementación de este procedimiento podría ser:
CREATE PROCEDURE
PrObraPorAutor @nombre_autor varchar(100)
AS
SELECT titulo,
descripcion, cantidad
FROM tbLibros
WHERE nombre_autor = @nombre_autor
Definición de los triggers
Si deseamos llevar un control
sobre el préstamo de libros y queremos evitar que cada usuario pueda tener más de un libro simultáneamente.
Cada vez que se realiza un préstamo nuevo la aplicación solicitará la inserción
de una nueva fila en la tabla de préstamos.
Necesitamos escribir un
trigger que tras cada inserción en esta tabla busque si existe alguna fila en
la base de datos que corresponda a un préstamo al mismo socio, y que elimine el
nuevo préstamo si ya tiene prestado un libro y no lo ha devuelto. En vez de
dejar que lo chequee la aplicación es más adecuado que la validación se realice
directamente sobre los datos para que estos sean coherentes en todo momento. En el ejemplo, se
lleva a cabo la inserción condicional de un préstamo.
CREATE TRIGGER
Tr_prestamo_condicional ON dbo.tbPrestamos
FOR INSERT AS IF
(SELECT COUNT(*) FROM dbo.tbPrestamos,
inserted
WHERE TbPrestamos.idSocio
= inserted.idSocio) <> @@ROWCOUNT
BEGIN DELETE
TbPrestamos WHERE TbPrestamos.idPrestamo =
inserted.idprestamo
END
Esta es sólo una posibilidad,
otra posibilidad sería gestionar la correspondencia entre la tabla libros y la
tabla autores cuando se da de baja un ejemplar cualquier circunstancia. En este caso el trigger debe
examinar, tras la eliminación de un libro, si el autor del libro eliminado
tiene aún algún volumen en el fondo de la biblioteca, eliminando su entrada en
la tabla autores si no es así.
El trigger sería de este modo:
CREATE TRIGGER Tr_comprobar_autor
ON dbo.Tblibros
FOR DELETE AS IF
(SELECT COUNT(*) FROM dbo.Tblibros
WHERE dbo.Tblibros.idautor = deleted. idautor ) > O
BEGIN DELETE dbo.TbAutores WHERE dbo.TbAutores.idautor = deleted.idautor
END
No hay comentarios:
Publicar un comentario