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.
INSERT INTO PASAJERO VALUES ('P0012', 'PAULA VALTIERRA','0010','3333333333', 'pau_val@hotmail.com')
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.
Una vez anulada la tabla INSERTED se puede obtener el valor de IDPAIS desde la tabla PAIS
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.
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:
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
DECLARE @PAI VARCHAR(30), @IDPA CHAR(5)
SELECT @PAI=PAIS FROM INSERTED
ROLLBACK
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
La imagen siguiente muestra el resultado de la inserción del país Guatemala que no se encontraba en la tabla PAIS
Finalmente, se anula el proceso e imprime el mensaje solicitado para el caso:
Para probar la eliminación ejecutaremos el siguiente script:
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');
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))
ROLLBACK TRANSACTION
PRINT 'EL PASAJERO TIENE PAGOS REGISTRADOS, NO PUEDE ELIMINARSE'
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
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
Finalmente comprobamos que se ingresó correctamente y también se replico.
INSERT INTO PASAJERO
VALUES('P0016','EMMANUEL URIAS','0010','98888888','LEUR0985@GMAIL.COM')
GO
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
un favor tienes la forma correcta de como meterlos en mysql
ResponderBorrar