miércoles, 16 de marzo de 2016

Triggers (Disparadores) en SQL SERVER

Buenos días queridos lectores, el día de hoy les escribiré de los triggers, o disparadores en SQL Server, un tema por demás interesante. Vamos a ver un poco de teoría sobre lo que es un disparador y después pasaremos a la práctica que es como mejor se aprende.

Triggers

Un trigger (o disparador) en una base de datos es un procedimiento que se ejecuta cuando se cumple una determinada condición al realizar una operación, dependiendo de la base de datos, los triggers pueden ser de inserción (INSERT), actualización (UPDATE) o borrado (DELETE). Algunas bases de datos pueden ejecutar triggers al crear, borrar o editar usuarios, tablas, bases de datos u otros objetos.

Usos:

  • Nos permite registrar, auditar y monitorear los procesos de cambio de valores a las tablas de la base de datos activas.
  • Puede validar los valores aprobando o negando acciones realizadas por las sentencias de tipo DML.
  • Puede preservar la consistencia y claridad de los valores, ejecutando acciones relacionadas con los objetos de tipo tabla de la base de datos activa.

Ventajas:

  • Un trigger ofrece chequeos de seguridad en valores con respecto a los objetos, tablas de una base de datos.
  • Fuerzan restricciones dinámicas de integridad de datos y de integridad referencial
  • Aseguran que las operaciones relacionadas se realizan juntas de forma implícita
  • Ofrecen un mayor control sobre los objetos de una base de datos

Desventajas:

  • Se tiene que programar anticipadamente lo que tiene que realizar un trigger
  • Siendo un procedimiento no se puede invocar directamente
  • Los triggers siempre serán creados para un conjunto de registros y no para uno solo ya que se dispara por operación DML
  • Los triggers no son aplicables a tablas temporales
Hay dos tipos de triggers:
  • Triggers DML
  • Trigger DDL
Sintaxis para Triggers DML

IMPLEMENTACION DE TRIGGER
CREATE TRIGGER
CREATE TRIGGER NOMBRE_TRIGGER
ON [TABLE | VIEW ]
FOR | AFTER |INSTEAD OF
[ INSERT ][ , ][ UPDATE ][  ,] [ DELETE ]
AS
SENTENCIA_SQL
Sintaxis para triggers DDL

IMPLEMENTACION DE TRIGGER
CREATE TRIGGER
CREATE TRIGGER NOMBRE_TRIGGER
ON [ALL SERVER | DATABASE ]
FOR | AFTER
AS
SENTENCIA_SQL
Donde:
  • TABLE/VIEW: Es la tabla o vista en la que se ejecuta el desencadenador DML; algunas veces se denomina tabla del desencadenador o vista del desencadenador. Especificar el nombre completo de la tabla o vista es opcional. Solo se puede hacer referencia a una vista mediante un desencadenador INSTEAD OF.
  • Database: Aplica el ámbito de un desencadenador DDL a la base de datos actual. Si se especifica, el desencadenador se activa cada vez que event_type o event_group tienen un lugar en la base de datos actual.
  • FOR | AFTER: AFTER especifica que el desencadenador DML sólo se activa cuando todas las operaciones especificadas en la instrucción SQL desencadenadora se han ejecutado correctamente. Además, todas las acciones referenciales en cascada y comprobaciones de restricciones deben ser correctas para que este desencadenador se ejecute. AFTER es el valor predeterminado cuando solo se especifica la palabra FOR. Los desencadenarores AFTER no se pueden definir en las vistas.
  • INSTEAD OF: Especifica que se ejecuta el desencadenador DML en vez de la instrucción SQL desencadenadora, por lo que se suplantan las acciones de las instrucciones desencadenadoras. INSTEAD OF no se puede especificar para desencadenadores DDL.Como máximo, se puede definir un desencadenador INSTEAD OF por cada instrucción INSERT, UPDATE o DELETE en cada tabla o vista. No obstante, en las vistas es posible definir otras vistas que tengan su propio desencadenador INSTEAD OF.
Opciones del TRIGGER
  • Mostrar todos los triggers de la base de datos
SELECT * FROM SYS.TRIGGERS
GO

  • Eliminar un trigger
IF OBJECT_ID('MENSAJEPASAJERO') IS NOT NULL
BEGIN
       DROP TRIGGER MENSAJE_PASAJERO
       PRINT 'TRIGGER ELIMINADO CORRECTAMENTE'
END
ELSE
       PRINT 'TRIGGER NO EXISTE'
GO

  • Inhabilitar un trigger: La inhabilitación indica que la tabla podrá ejecutar sus sentencias DML sin mayor control del trigger inhabilitado
DISABLE TRIGGER MENSAJEXNUEVOPASAJERO
       ON PASAJERO
GO

  • Habilitar un trigger:
ENABLE TRIGGER MENSAJEXNUEVOPASAJERO
       ON PASAJERO
GO

  • Inhabilitar todos los triggers de una tabla
ALTER TABLE PASAJERO
       DISABLE TRIGGER ALL
GO

Pero basta de teoría queridos lectores, vamos a realizar algunos ejemplos que es como se aprende

Ejemplo 1:
Implemente un trigger que permita mostrar un mensaje cada vez que se inserte o actualice un registro en la tabla PASAJERO.



CREATE TRIGGER MENSAJE_PASAJERO
ON PASAJERO
FOR INSERT, UPDATE
AS
PRINT 'MENSAJE DISPARADO DE LA INSERCION O ACTUALIZACION DE LA TABLA PASAJERO'
GO

En el script se implementa un trigger que por insertar un nuevo registro (INSERT INTO) o actualizarlo (UPDATE) se debe mostrar el mensaje enviado desde la función PRINT.

Para probar el trigger se tiene que registrar un nuevo pasajero colocando el siguiente script:

 INSERT INTO PASAJERO VALUES ('P0012', 'PAULA VALTIERRA','0010','3333333333', 'pau_val@hotmail.com')
GO

La siguiente imagen muestra el resultado de la inserción del nuevo pasajero:


Observe que desde el trigger se envía el mensaje y desde el motor de base de datos se envía ("1 fil afectadas")

Ejemplo 2:
Implemente un trigger que permita crear un histórico de los registros realizados a la tabla PAGO, en la cual por cada registro de pago realizado por un pasajero se deberá enviar el IDPASAJERO y el conteo total de pagos realizados por dicho pasajero a una tabla nueva llamada CUENTAPAGOXPASAJERO

--1
CREATE TABLE CUENTAPAGOXPASAJERO (IDPASAJERO CHAR(5) NOT NULL, TOTAL INT)
GO

--2
IF OBJECT_ID('TX_CUENTAPAGOXPASAJERO') IS NOT NULL
BEGIN
       DROP TRIGGER TX_CUENTAPAGOXPASAJERO
       PRINT 'TRIGER ELIMINADO CORRECTAMENTE'
END
ELSE
       PRINT 'TRIGGER NO EXISTE'
GO

--3
CREATE TRIGGER TX_CUENTAPAGOXPASAJERO
ON PAGO
AFTER INSERT
AS
       DECLARE @TOTAL INT, @PAS VARCHAR(40)
       SELECT @TOTAL = COUNT(*)
             FROM INSERTED
             INNER JOIN PAGO ON PAGO.IDPASAJERO=inserted.IDPASAJERO

       SELECT @PAS=INSERTED.IDPASAJERO FROM INSERTED

       IF EXISTS (SELECT * FROM CUENTAPAGOXPASAJERO WHERE IDPASAJERO=@PAS)
             UPDATE CUENTAPAGOXPASAJERO
                    SET TOTAL=@TOTAL
                    WHERE IDPASAJERO=@PAS
       ELSE
             INSERT INTO CUENTAPAGOXPASAJERO VALUES(@PAS,@TOTAL)
GO

En el punto uno se implementa el script de creación de la tabla histórica llamada CUENTAPAGOXPASAJERO, hay que tener en cuenta que solo tendrá dos columnas, en una se enconrará el IDPASAJERO registrado y en otra columna el total de pagos realizados por el mismo pasajero

En el punto dos se implementa el script que determinará si el trigger existe en la base para poder eliminarlo enviando el mensaje de confirmación en ambos casos.

En el punto tres se implementa el script que permite crear el trigger TX_CUENTAPAGOXPASAJERO, se debe especificar que las acciones se realizan dentro de la tabla PAGO (ON PAGO). Todas las acciones del trigger deben de ocurrir  solo después de insertado un registro en la tabla PAGO par tal motivo la especificación AFTER INSERT.

Se declaran dos variables locales, @TOTAL tiene por misión obtener el número total de pagos realizados por un determinado pasajero y @PAS tiene por misión capturar el IDPASAJERO proveniente del registro realizado a la tabla PAGO. Por medio de la sentencia SELECT se registrarán los valores necesarios para la tabla CUENTAPAGOXPASAJERO.

DECLARE @TOTAL INT, @PAS VARCHAR(40)
       SELECT @TOTAL = COUNT(*)
             FROM INSERTED
             INNER JOIN PAGO ON PAGO.IDPASAJERO=inserted.IDPASAJERO

La tabla INSERTED proviene de los datos que se intentan registrar a la tabla PAGO. Se hizo una comparación entre IDPASAJERO de la tabla INSERTED y la tabla PAGO para poder realizar un conteo verídico del pasajero.

Luego se tiene que capturar el IDPASAJERO obtenido desde la tabla INSERTED, este valor es importante, ya que es la única forma de comprobar si hay duplicidad en la nueva tabla histórica llamada CUENTAPAGOXPASAJERO, esta tabla solo debe registrar un pasajero por vez y en caso lo encuentre mas veces, solo debe actualizar el valor del conteo, el siguiente script muestra como se captura dicho valor.

  SELECT @PAS=INSERTED.IDPASAJERO FROM INSERTED

Finalmente comparamos la existencia IDPASAJERO dentro de la nueva tabla CUENTAPAGOXPASAJERO, de acuerdo a la condición se actualizará el valor o se insertará dependiendo del código del pasajero.

IF EXISTS (SELECT * FROM CUENTAPAGOXPASAJERO WHERE IDPASAJERO=@PAS)
             UPDATE CUENTAPAGOXPASAJERO
                    SET TOTAL=@TOTAL
                    WHERE IDPASAJERO=@PAS
       ELSE
             INSERT INTO CUENTAPAGOXPASAJERO VALUES(@PAS,@TOTAL)

Para probar el trigger debemos ingresar registros en la tabla PAGO, para este caso se está insertando 5 registros; ejecute el siguiente script:

INSERT INTO PAGO VALUES(4,2,'P0002','08/10/2011',200)
GO
INSERT INTO PAGO VALUES(5,2,'P0002','10/10/2011',300)
GO
INSERT INTO PAGO VALUES(6,6,'P0009','18/12/2011',1000)
GO
INSERT INTO PAGO VALUES(7,6,'P0009','19/12/2011',700)
GO
INSERT INTO PAGO VALUES(8,6,'P0009','20/12/2011',200)
GO

Ahora mostraremos como se ve inicialmente la tabla PAGO


Luego de ingresar los cinco registros queda de la siguiente manera:


Finalmente, la imagen siguiente muestra el contenido de la tabla CUENTAPAGOXPASAJERO

Ejemplo 3
Implemente un trigger que permita controlar los registros de la tabla PAIS si el nombre de dicho país fue registrado entonces deberá mostrar con que código fue registrado, caso contrario emitir un mensaje de PAIS REGISTRADO CORRECTAMENTE.

IF OBJECT_ID('VALIDAPAIS') IS NOT NULL
BEGIN
       DROP TRIGGER VALIDAPAIS
       PRINT 'TRIGGER ELIMINADO CORRECTAMENTE'
END
ELSE
       PRINT 'TRIGGER NO EXISTE'
GO 

CREATE TRIGGER VALIDAPAIS
ON PAIS
FOR INSERT
AS
       IF (SELECT COUNT(*) FROM INSERTED
             INNER JOIN PAIS ON PAIS.PAIS=INSERTED.PAIS
             WHERE INSERTED.PAIS=PAIS.PAIS)>1
       BEGIN
             DECLARE @PAI VARCHAR(30),@IDPA CHAR(5)
             SELECT @PAI=PAIS FROM INSERTED
            
             ROLLBACK
            
             SELECT @IDPA=IDPAIS FROM PAIS
                    WHERE PAIS.PAIS=@PAI
            
             PRINT 'NOMBRE DE PAIS YA REGISTRADO EN LA TABLA'
             PRINT ''
             PRINT 'EL PAIS '+@PAI+' SE ENCUENTRA REGISTRADO CON EL CODIGO: '+@IDPA
            
       END
       ELSE
             PRINT 'PAIS REGISTRADO CORRECTAMENTE'

GO

En el punto uno se implementa un script que permite eliminar el trigger si ya se encontraba dentro de la base de datos.

En el punto dos se implementa el script para el trigger VALIDAPAIS en el cual se especifica que se trabajará en la tabla PAIS y en el evento de insertar un registro:

CREATE TRIGGER VALIDAPAIS
ON PAIS
FOR INSERT

Luego se condiciona que el país que intenta registrar no se encuentre en la tabla, en este caso se uso la función COUNT para determinar el número de veces que dicho país se encuentra registrado, en caso supere uno quiere decir que es la segunda vez que intenta registrarse y esto sucede porque el registro del nuevo país se graba primero en tabla INSERTED

IF (SELECT COUNT(*) FROM INSERTED
       INNER JOIN PAIS ON INSERTED.IDPAIS=PAIS.IDPAIS)>1

Luego declaramos dos variables, la primera @PAI tiene por misión obtener el nombre del país que se intenta registrar, lo obtendremos desde la tabla INSERTED, la segunda @IDPA tiene por misión guardar el código del país desde la tabla PAIS, es decir, buscarlo dentro de la tabla PAIS y mostrar en que código se encuentra registrado. Aquí hay algo importante que mencionar, dicho código de país no es tan sencillo de buscar ya que existen dos códigos similares que son INSERTED.IDPAIS y PAIS.IDPAIS, por lo tanto, para no generar ambigüedades es que se invoca a la instrucción ROLLBACK que permitirá anular la intervención de la tabla INSERTED quedando solo la tabla país para la búsqueda.

 DECLARE @PAI VARCHAR(30), @IDPA CHAR(5)
             SELECT @PAI=PAIS FROM INSERTED
             ROLLBACK

Una vez anulada la tabla INSERTED se puede obtener el valor de IDPAIS desde la tabla PAIS


 SELECT @IDPA = IDPAIS FROM PAIS  

 WHERE PAIS.PAIS = @PAI

Finalmente, se imprimen los resultados obtenidos, para este caso, se imprime el mensaje, según el caso, además del país y su código encontrado.

Para probar el trigger se necesita ejecutar el siguiente script


INSERT INTO PAIS VALUES ('0016','CHILE');

La siguiente imagen muestra el resultado de la inserción del país Chile

INSERT INTO PAIS VALUES ('0017','GUATEMALA');

La imagen siguiente muestra el resultado de la inserción del país Guatemala que no se encontraba en la tabla PAIS


Ejemplo 4
Implemente un trigger que permita controlar la eliminación de un registro de la tabla PASAJERO, en la cual si dicho pasajero tiene un pago realizado, no permita su eliminación mostrando un mensaje, caso contrario, mostrar el mensaje de eliminación correcta.



--1
IF OBJECT_ID('ELIMINAPASAJERO') IS NOT NULL
BEGIN
       DROP TRIGGER ELIMINAPASAJERO
END
GO

--2
CREATE TRIGGER ELIMINAPASAJERO
ON PASAJERO
INSTEAD OF DELETE
AS
       IF EXISTS(SELECT * FROM PAGO
                           WHERE PAGO.IDPASAJERO=(SELECT DELETED.IDPASAJERO
                                                                                 FROM ELETED))
       BEGIN
             ROLLBACK TRANSACTION
             PRINT 'EL PASAJERO TIENE PAGOS REGISTRADOS, NO PUEDE ELIMINARSE'
       END
       ELSE
             PRINT 'PASAJERO ELIMINADO CORRECTAMENTE'
GO

En el punto uo se implementa el script que permitirá verificar si el trigger ya existe, en caso lo sea, lo eliminará para volverlo a crear.

En el punto dos se implementa el trigger ELIMINAPASAJERO direccionando a la tabla PASAJERO que es la involucrada en el caso. Luego se condiciona a que el trigger sea el responsable de la eliminación del registro solicitado, ya que la verificación del IDPASAJERO se tiene que realizar en la tabla PAGO, porque el caso menciona que se eliminará solo si no tiene pagos realizados, entonces para este caso se usa INSTEAD OF DELETE, esto quiere decir que no se ejecutará la sentencia DELETE hasta que termine de validar el trigger.

CREATE TRIGGER ELIMINAPASAJERO
ON PASAJERO
INSTEAD OF DELETE

Luego verificamos la existencia del pasajero en la tabla PAGO, todo esto será realizado por medio del operador EXISTS.


       IF EXISTS(SELECT * FROM PAGO
                           WHERE PAGO.IDPASAJERO=(SELECT DELETED.IDPASAJERO
                                                                                 FROM ELETED))

Finalmente, se anula el proceso e imprime el mensaje solicitado para el caso:

ROLLBACK TRANSACTION
             PRINT 'EL PASAJERO TIENE PAGOS REGISTRADOS, NO PUEDE ELIMINARSE'

Para probar la eliminación ejecutaremos el siguiente script:

DELETE PASAJERO WHERE IDPASAJERO = 'P0003'

La imagen siguiente muestra el resultado de la eliminación:


El pasajero con código P003 si tiene pagos realizados; por lo tanto, no puede ser eliminado

Ejemplo 5
Implemente un trigger que permita controlar el registro de un pago en la cual se evalúe el monto registrado para que no se registre un valor inferior a cero en la columna Monto.



--1
IF OBJECT_ID('VALIDAPAGO') IS NOT NULL
BEGIN
       DROP TRIGGER VALIDAPAGO
END
GO

--2
CREATE TRIGGER VALIDAPAGO
ON PAGO
FOR INSERT
AS
       IF (SELECT MONTO FROM INSERTED)<=0
       BEGIN
             ROLLBACK TRANSACTION
             PRINT 'NO SE PUEDE REGISTRAR MONTO CERO'
       END
       ELSE
             PRINT 'PAGO REGISTRADO CORRECTAMENTE'
GO


En el punto uno se implementa el script que permitirá determinar si el trigger existe o no en la base de datos.

En el punto dos se implementa el trigger VALIDAPAGO donde se involucra a la tabla PAGO, ya que es allí donde se registrarán los pagos de los pasajeros. En el trigger se tiene que condicionar que el monto no sea cero ya que no tiene sentido que un pasajero cancele un pago con monto cero, aquí se envía un mesaje de error y se cancela la transacción

IF (SELECT MONTO FROM INSERTED)<=0
       BEGIN
             ROLLBACK TRANSACTION
             PRINT 'NO SE PUEDE REGISTRAR MONTO CERO'
       END
       ELSE
             PRINT 'PAGO REGISTRADO CORRECTAMENTE'
GO

Ejemplo 6
Implemente un trigger que permita crear una réplica de los registros insertados en la tabla PASAJERO, para dicho proceso debe implementar una nueva tabla llamada PasajeroBAK con las mismas columnas que la tabla PASAJERO.

--1
IF OBJECT_ID('PASAJEROSBAK') IS NOT NULL
BEGIN
       DROP TABLE PASAJEROSBAK
END
GO
--2
CREATE TABLE PASAJEROSBAK(
       IDPASAJERO    CHAR(5)                    NOT NULL PRIMARY KEY,
       NOMBRES             VARCHAR(50)         NOT NULL,
       IDPAIS        CHAR(4)                    NOT NULL,
       TELEFONO      CHAR(15)            NOT NULL,
       EMAIL         VARCHAR(50)         NOT NULL     
)
GO
--3
IF OBJECT_ID('REPLICAPASAJERO') IS NOT NULL
BEGIN
       DROP TRIGGER REPLICAPASAJERO
END
GO
--4
CREATE TRIGGER REPLICAPASAJERO
ON PASAJERO
AFTER INSERT
AS
BEGIN
       INSERT PASAJEROSBAK
             SELECT * FROM INSERTED
END

GO

En el punto uno se implementa el script para verificar que la tabla PASAJEROSBAK no existe, tenga en cuenta que este script solo debe ejecutarse una vez, ya que luego si tendrá registros.

En el punto dos se implementa el script que permitirá crear la tabla PASAJEROSBAK con las mismas columnas que la tabla PASAJERO.

En el punto tres se verifica que el trigger no exista, en caso sea así se eliminará de la base de dato.

En el punto cuatro se implementa el trigger REPLICAPASAJERO que tiene por misión grabar los valores provenientes de la tabla INSERTED hacia la tabla PASAJEROSBAK, considere que esto se realiza después de insertar en su tabla original, por eso usamos la instrucción AFTER INSERT.

Primero veamos antes de la inserción como se encontraban las tablas involucradas.



Ahora ejecutaremos el script que permite insertar un registro a la tabla PASAJERO

INSERT INTO PASAJERO
       VALUES('P0016','EMMANUEL URIAS','0010','98888888','LEUR0985@GMAIL.COM')

GO


Finalmente comprobamos que se ingresó correctamente y también se replico.

Hasta aquí el post sobre disparadores en Transact SQL, solo nos falta un tema para terminar este minicurso de SQL, y falta el tema de "XML con SQL SERVER" que será abordado en próximos post. Espero haya sido de su utilidad; comenten, compartan o den clic en algún anuncio que les agrade, cualquier duda pueden preguntar.

Saludos




Banderas de Visitantes

Flag Counter