Aspectos básicos
Los triggers son un tipo
especial de procedimientos almacenados que se ejecutan automáticamente al
producirse una modificación de una tabla. Los triggers poseen la utilidad de
integrar en la base de datos decisiones de negocio asociadas a los propios
datos, sin que sea necesario programar procedimientos externos. Permiten
mantener coherencia semántica en la base de datos.
La sentencia SQL para crear
triggers es CREATE TRIGGER. Para crear un trigger es
necesario especificar la tabla cuyas modificaciones activarán su ejecución y las
acciones que se llevarán a cabo en tal caso, en forma de sentencias SQL. Los
triggers están vinculados de manera inseparable a la tabla que provoca su
ejecución, de tal forma que sólo el
propietario de una tabla puede definir un trigger asociado a esta misma. Solo
pueden crearse triggers asociados a tablas de la base de datos actualmente
seleccionada.
Según el tipo de evento que
los desencadena se clasifican en:
Desencadenadores DML
Desencadenadores DDL
Desencadenadores LOGON
Los desencadenadores 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.
Los desencadenadores DDL se
ejecutan en respuesta a una variedad de eventos de lenguaje de definición de
datos (DDL). Estos eventos corresponden principalmente a instrucciones CREATE,
ALTER y DROP de SQL, y a determinados procedimientos almacenados del sistema
que ejecutan operaciones de tipo DDL.
Los desencadenadores LOGON se
activan en respuesta al evento LOGON que se genera cuando se establece la
sesión de un usuario.
Este estudio se limita a los
desencadenadores DML.
Creación
de triggers mediante la sentencia CREATE TRIGGER
La sentencia simplificada para
crear un trigger vinculado a una determinada tabla es la siguiente:
CREATE TRIGGER [ propietario .
]nombre_de_trigger
ON { tabla | vista }
[ WITH <dml_opcion_de_trigger> [
,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [
DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sentencia_SQL [ ; ] [ ,...n ] | EXTERNAL NAME <especificador_de_metodo
[ ; ] > }
Aquí un ejemplo de creación de
un trigger.
Este Trigger muestra un
mensaje cuando alguien intenta agregar o cambiar datos en la tabla tbTabla de la
base de datos.
IF OBJECT_ID ('dbo.tr_nombre_Trigger',
'TR') IS NOT NULL
DROP TRIGGER dbo.tr_nombre_Trigger;
GO
CREATE TRIGGER
tr_nombre_Trigger
ON dbo.tbTabla
AFTER INSERT, UPDATE
AS
--Aquí va la definición de las
acciones a ejecutar. En este caso --sólo muestra un mensaje
RAISERROR ('Notificación', 16,
10);
GO
En la creación se especifica
el nombre de Ia tabla a la que se desea asociar el trigger, así como un nombre para
el propio trigger.
Acciones que desatan la ejecución del trigger
El trigger se ejecuta como
respuesta a ciertas acciones sobre Ia tabla asociada al mismo. Estas sentencias
Se especifican en Ia cláusula FOR UPDATE/INSERT/DELETE. Cuando se Ileven a cabo
las acciones que se especifiquen en la definición del trigger sobre la tabla, se
ejecutarán las previamente establecidas en el trigger.
Ejecución condicional
La cláusula IF UPDATE nombre_columna,
condiciona la ejecución de las sentencias del trigger a que una o varias columnas
de la tabla asociada sean actualizadas mediante INSERT o UPDATE.
Encriptación
Igual que los procedimientos
almacenados, también es posible encriptar la descripción del trigger en la
tabla syscomments utilizando la cláusula WITH ENCRYPTION. Esto suele hacerse
para evitar que se pueda modificar el trigger por un programador externo, por
ejemplo desde una aplicación. No se debe
utilizar esta opción si se migra la base de datos pues no se podrá recrear el
trigger en la nueva base de datos.
Sentencias SQL del trigger
El cuerpo del trigger constará
de una serie de sentencias SQL que realizaran las tareas que esperamos del
mismo y una serie de condiciones determinarán de manera adicional si las
acciones del trigger deben ser llevadas a cabo o no.
Sentencias no permitidas
Las siguientes instrucciones de SQL no están permitidas en un trigger:
ALTER DATABASE
|
CREATE DATABASE
|
DROP DATABASE
|
RESTORE DATABASE
|
RESTORE LOG
|
RECONFIGURE
|
Además, las siguientes
instrucciones de SQL no se permiten incluir en el cuerpo de un trigger cuando
este se usa en la tabla o la vista que es objeto de la acción desencadenadora.
CREATE INDEX (incluidos CREATE SPATIAL INDEX y CREATE XML INDEX)
|
ALTER INDEX
|
DROP INDEX
|
DBCC DBREINDEX
|
ALTER PARTITION FUNCTION
|
DROP TABLE
|
ALTER TABLE cuando se utiliza para:
Agregar, modificar o quitar columnas.
|
Aquí un ejemplo de un trigger
más complejo.
Como en las restricciones CHECK sólo hacen referencia a las columnas en
las que se han definido las restricciones de columna o de tabla, cualquier
restricción entre tablas, deben definirse como triggers.
El trigger comprueba la solvencia del cliente cuando se intenta realizar
una venta a un cliente. Para revisar la solvencia del cliente, hace
referencia a la tabla tbClientes donde lee el crédito del que dispone el
cliente. Si la solvencia no es satisfactoria, muestra un mensaje y no se
ejecuta la inserción.
IF OBJECT_ID ('dbo.TrCreditoBajo','TR') IS NOT NULL
DROP TRIGGER
dbo.TrCreditoBajo;
GO
-- Este trigger evita
que se inserte una fila en la tabla tbOrdenVentas
-- cuando el crédito
del cliente es inferior a 10.000
CREATE TRIGGER dbo.TrCreditoBajo
ON dbo.tbOrdenVentas
AFTER INSERT
AS
IF EXISTS (SELECT *
FROM dbo.tbOrdenVentas
AS o
JOIN
tbVentas_aceptadas AS v
ON o.IdOrdenVenta = v.IdOrdenVenta
JOIN
model.tbClientes AS
c
ON c.IdCliente = o.idCliente
WHERE
c.Credito_Cliente <
10000
)
BEGIN
RAISERROR ('El credito del cliente
es inferior a 10.000, venta no aceptada.', 16, 1);
ROLLBACK TRANSACTION;
RETURN
END;
GO
-- Si no se cumple la
condición, es decir si el cliente posee más --de 10.000 de crédito se inserta
un registro en la tabla de órdenes de venta tbOrdenVentas.
INSERT INTO dbo.tbOrdenVentas (NumeroRevision, Status, Id_Vendedor,
Id_Cliente, Id_Tipo_Venta, Fecha_Venta, Total, Impuestos)
VALUES (
5
,9
,7518
,1759
,3
,GETDATE()
,332444.54
,3324.4);
GO
Creación de Triggers con el Administrador Corporativo
Para crear triggers con el
Administrador Corporativo basta colocarse sobre el explorador de objetos en la
carpeta de Desencadenadores de Bases de
Datos que depende de la carpeta Programación.
Una vez posicionados sobre
dicha carpeta pulsamos el botón derecho del ratón y nos muestra la opción de
crear un trigger (desencadenador).
Esto genera una plantilla
predefinida sobre la que escribiremos nuestros
requerimientos particulares.
--====================================
--
Create database trigger template
--====================================
USE <database_name, sysname, AdventureWorks>
GO
IF EXISTS(
SELECT *
FROM sys.triggers
WHERE name = N'<trigger_name, sysname, table_alter_drop_safety>'
AND parent_class_desc =
N'DATABASE'
)
DROP TRIGGER <trigger_name, sysname,
table_alter_drop_safety> ON DATABASE
GO
CREATE TRIGGER <trigger_name, sysname,
table_alter_drop_safety> ON DATABASE
FOR <data_definition_statements,, DROP_TABLE,
ALTER_TABLE>
AS
IF IS_MEMBER ('db_owner') = 0
BEGIN
PRINT 'You must ask your DBA
to drop or alter tables!'
ROLLBACK TRANSACTION
END
GO
Eliminación de triggers
Igual que para crear un
trigger su eliminación puede utilizarse uno de los dos métodos anteriores. La
eliminación a través de la Cláusula DROP TRIGGER o a través del administrador
corporativo.
Con la sentencia
DROP TRIGGER.
DROP TRIGGER [schema_name.]trigger_name [
,...n ] [ ; ]
Ejemplo:
DROP TRIGGER dbo. dbo.TrCreditoBajo
Mediante el
administrador corporativo.
Una vez creado un trigger no
aparece debajo de la carpeta de
desencadenadores como cabría esperar y como sucede con otros objetos de la base
de datos como los procedimientos almacenados. En este caso para ver los
triggers creados hay que desplegar las tablas y sobre la tabla para la que se
ha definido el trigger pulsando en el icono + se despliegan las columna y
atributos de la tabla en cuestión, en la carpeta desencadenadores de la tabla
se muestran los triggers asociados a ella.
Para modificar o eliminar un
trigger, basta con posicionarse sobre él y pulsar el botón derecho el ratón
para que se muestre el menú en el que podremos elegir modificar el trigger o
eliminarlo.
Sólo el propietario de la tabla puede eliminar los triggers asociados a esta. Si se elimina la tabla automáticamente se eliminan todos los triggers que tenga a sociados.
Sólo el propietario de la tabla puede eliminar los triggers asociados a esta. Si se elimina la tabla automáticamente se eliminan todos los triggers que tenga a sociados.
Muchas gracias, quedo todo claro.
ResponderEliminar