Download Trigger Los triggers o disparadores son objetos de la base de datos

Trigger
Un trigger(o desencadenador) es una clase especial de procedimiento almacenado que se ejecuta
automáticamente cuando se produce un evento en el servidor de bases de datos.
Los triggers o disparadores son objetos de la base de datos que ejecutan acciones cuando se
producen ciertos eventos (tanto DML como DDL) (inserciones, modificaciones, borrados, creación
de tablas, etc.).


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 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 Transact-SQL, y a determinados procedimientos almacenados del sistema que ejecutan
operaciones de tipo DDL.
La estructura básica de un trigger es:
Llamada de activación: es la sentencia que permite "disparar" el código a ejecutar. Restricción: es
la condición necesaria para realizar el código. Esta restricción puede ser de tipo condicional o de
tipo nulidad.
Acción a ejecutar: es la secuencia de instrucciones a ejecutar una vez que se han cumplido las
condiciones iniciales.
Existen dos tipos de disparadores que se clasifican según la cantidad de ejecuciones a realizar:
Row Triggers (o Disparadores de fila): son aquellas que se ejecutaran n-veces si se llama n-veces
desde la tabla asociada al trigger
Statement Triggers (o Disparadores de secuencia): son aquellos que sin importar la cantidad de
veces que se cumpla con la condición, su ejecución es única.
Características:
No aceptan parámetros o argumentos (pero podrían almacenar los datos afectados en tablas
temporales)
No pueden ejecutar las operaciones COMMIT o ROLLBACK por que estas son parte de la sentencia
SQL del disparador (únicamente a través de transacciones autónomas)
Pueden causar errores de mutaciones en las tablas, si se han escrito de manera deficiente.
Usos
Son usados para mejorar la administración de la Base de datos, sin necesidad de contar con que el
usuario ejecute la sentencia de SQL.
Además, pueden generar valores de columnas, previene errores de datos, sincroniza tablas,
modifica valores de una vista, etc.
Permite implementar programas basados en paradigma lógico (sistemas expertos, deducción).
Ventajas y desventajas:
- No pueden ser invocados directamente; al intentar modificar los datos de una tabla para la que
se ha definido un disparador, el disparador se ejecuta automáticamente.
- No reciben y retornan parámetros.
- Son apropiados para mantener la integridad de los datos, no para obtener resultados de
consultas.
-Pueden hacer referencia a campos de otras tablas.
-Los disparadores se ejecutan DESPUES de la ejecución de una instrucción "insert", "update" o
"delete" en la tabla en la que fueron definidos. Las restricciones se comprueban ANTES de la
ejecución de una instrucción "insert", "update" o "delete". Por lo tanto, las restricciones se
comprueban primero, si se infringe alguna restricción, el desencadenador no llega a ejecutarse.
-Mejor utilización de la CPU
-Menor necesidad de limpieza de las memorias intermedias durante el procesamiento de las
transacciones
-Puntos de verificación más rápidos
-Menor tiempo de recuperación
-SQL Server registra las transacciones de tal modo que las actualizaciones en una de ellas siempre
se puedan recuperar o reducir al último estado consistente si el equipo cliente o servidor falla.
Aunque el motor de base de datos Microsoft Jet y los archivos .mdb también proporcionan
transacciones, éstas no se administran mediante un registro de transacciones separado en los
archivos .mdb y pueden fallar sin posibilidad de recuperación si se daña el archivo de la base de
datos.
Sintaxis básica:
create triggre NOMBREDISPARADOR
on NOMBRETABLA
for EVENTO- insert, update o delete
as
SENTENCIAS
Ejemplos de triggers:
1.CREATE TRIGGER TR_CUENTAS
ON CUENTAS
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO HCO_SALDOS
(IDCUENTA, SALDO, FXSALDO)
SELECT IDCUENTA, SALDO, getdate()
FROM INSERTED
END
2.CREATE TRIGGER TR_RESULTADO
ON RESULTADO
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF UPDATE (SALDO) -- Solo si se actualiza SALDO
BEGIN
INSERT INTO HCO_SALDOS
(IDCUENTA, SALDO, FXSALDO)
SELECT IDCUENTA, SALDO, getdate()
FROM INSERTED
END
3.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
Procedimientos almacenados en Transact SQL
Un procedimiento es un programa dentro de la base de datos que ejecuta una acción o conjunto
de acciones específicas.
Un procedimiento tiene un nombre, un conjunto de parámetros (opcional) y un bloque de código.
En Transact SQL los procedimientos almacenados pueden devolver valores (numérico entero) o
conjuntos de resultados. Los procedimientos pueden:





Incluir parámetros
Llamar a otros procedimientos
Devolver un valor de estado a un procedimiento de llamada o lote para indicar el éxito o el
fracaso del mismo y la razón de dicho fallo
Devolver valores de parámetros a un procedimiento de llamada o lote
Ejecutarse en SQL Server remotos
Para crear un procedimiento almacenado debemos emplear la sentencia CREATE PROCEDURE.
CREATE PROCEDURE <nombre_procedure> [param1 <tipo>, ...]
AS
-- Sentencias
Para modificar un procedimiento almacenado debemos emplear la sentencia ALTER PROCEDURE.
ALTER PROCEDURE <nombre_procedure> [param1 <tipo>, ...]
AS
-- Sentencias
Usos
Los usos 'típicos' de los procedimientos almacenados se aplican en la validación de datos,
integrados dentro de la estructura del banco de datos. Los procedimientos almacenados usados
con tal propósito se llaman comúnmente disparadores, o triggers. Otro uso común es la
'encapsulación' de un API para un proceso complejo o grande que podría requerir la 'ejecución' de
varias consultas SQL, tales como la manipulación de un 'dataset' enorme para producir un
resultado resumido.
También pueden ser usados para el control de gestión de operaciones, y ejecutar procedimientos
almacenados dentro de una transacción de tal manera que las transacciones sean efectivamente
transparentes para ellos.
Ventajas
-La ventaja de un procedimiento almacenado, en respuesta a una petición de usuario, está
directamente bajo el control del motor del manejador de bases de datos, lo cual corre
generalmente en un servidor separado de manejador de bases de datos aumentando con ello, la
rapidez de procesamiento de requerimientos del manejador de bases de datos. El servidor de la
base de datos tiene acceso directo a los datos necesarios para manipular y sólo necesita enviar el
resultado final al usuario. Los procedimientos almacenados pueden permitir que la lógica del
negocio se encuentre como un API en la base de datos, que pueden simplificar la gestión de datos
y reducir la necesidad de codificar la lógica en el resto de los programas cliente. Esto puede reducir
la probabilidad de que los datos sean corrompidos por el uso de programas clientes defectuosos o
erróneos. De este modo, el motor de base de datos puede asegurar la integridad de los datos y la
consistencia, con la ayuda de procedimientos almacenados.
-Una señal de un sistema de base de datos es que evita que los usuarios accedan directamente a
las tablas y fuerzan a utilizar los P.A. Para funciones específicas.
-Es más fácil gestionar los conjuntos de P.A. Por funcionalidad que gestionar una tabla a nivel de
columnas.
Desventajas:
Puede que no sea el mejor lugar para poner una lógica compleja. Sin embargo, tras la idea de que
una lógica compleja pertenece en código de aplicación y no en procedimientos almacenados,
procedimientos almacenados se convierten simplemente en las operaciones CRUD (cada mesa
tiene un "Crear", "Leer", "actualización" y "Borrar" procedimiento). En ese caso, los
procedimientos almacenados no añade ningún valor a la demanda, sólo complicar el
mantenimiento y la convierten en residuos.
Ejemplos:
1.CREATE PROCEDURE spu_addCliente @nombre varchar(100),
@apellido1 varchar(100),
@apellido2 varchar(100),
@nifCif varchar(20),
@fxNaciento datetime
AS
INSERT INTO CLIENTES
(nombre, apellido1, apellido2, nifcif, fxnacimiento) VALUES
(@nombre, @apellido1, @apellido2, @nifCif, @fxNaciento)
DECLARE @fecha_nacimiento datetime
set @fecha_nacimiento = convert(datetime, '13/05/1975', 103)
EXEC spu_addCliente 'Pedro', 'Herrarte', 'Sanchez',
'00000002323', @fecha_nacimiento
2.CREATE PROCEDURE spu_ObtenerSaldoCuenta @numCuenta varchar(20),
@saldo decimal(10,2) output
AS
BEGIN
SELECT @saldo = SALDO
FROM CUENTAS
WHERE NUMCUENTA = @numCuenta
END
Y para ejecutar este procedure:
DECLARE @saldo decimal(10,2)
EXEC spu_ObtenerSaldoCuenta '200700000001', @saldo output
PRINT @saldo
3.CREATE PROCEDURE spu_EstaEnNumerosRojos @numCuenta varchar(20)
AS
BEGIN
IF (SELECT SALDO FROM CUENTAS
WHERE NUMCUENTA = @numCuenta) < 0
BEGIN
RETURN 1
END
ELSE
RETURN 0
END
DECLARE @rv int
EXEC @rv = spu_EstaEnNumerosRojos '200700000001'
PRINT @rv