Triggers SQL
Todos estaremos de acuerdo en que una de las cosas más importantes en todo negocio son los datos, ¿verdad?. Pues bien, imagine una base de datos sobre la que interactuan concurrentemente muchos usuarios a través de distintas aplicaciones, web o de escritorio. ¿Qué sucedería si una de estas aplicaciones gestionase los datos incorrectamente?.
Por ejemplo, imagine una aplicación de escritorio que interactua directamente a través de JDBC con la BD y que dicha aplicación usa la hora de la máquina del usuario como hora en la que se realizan las operaciones, ¿terrible, verdad?.. pues bien, estos y otros problemas pueden ser solucionados con mecanismos como los triggers o disparadores de BD (como veremos en los ejemplos).
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).
A continuación y a modo de consulta os voy a mostrar un ejemplo de un Trigger DML que realiza las siguientes tareas:
Dada una tabla con información sobre «expedientes», vamos a crear un Trigger que controle las modificaciones del «estado del expediente» de la siguiente manera:
- Anotará en el campo «stateChangedDate» la fecha/hora en la que se produjo un cambio de estado.
- A modo de histórico, insertará un registro en tabla «expStatusHistory» con información sobre los cambios de estado de cada expediente.
Fácil ¿verdad?, pues bueno, mamos a la obra.
Ejemplo en MySql.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
DELIMITER $$ USE db_test; $$ # Creamos el Schema si no existe CREATE SCHEMA IF NOT EXISTS db_test; $$ -- Eliminamos el procedimiento almancenado si existise DROP PROCEDURE IF EXISTS db_test.procedureTemp; $$ CREATE PROCEDURE db_test.procedureTemp() BEGIN DECLARE cuenta INT DEFAULT 0; -- Si no existe la tabla de expedientes, la creamos. SELECT COUNT(*) INTO cuenta FROM `information_schema`.`tables` WHERE TABLE_SCHEMA='db_test' AND TABLE_NAME='expedientes' LIMIT 1; IF (cuenta = 0) THEN CREATE TABLE `expedientes` ( code VARCHAR(15) NOT NULL COMMENT 'Código del expediente', state VARCHAR(20) COMMENT 'Estado del expediente', stateChangedDate DATETIME COMMENT 'Fecha/Hora en la que se produció el último cambio de estado', PRIMARY KEY `PK_Exp` (code) ) ENGINE=InnoDB CHARSET=utf8 collate=utf8_general_ci; END IF; -- Insertamos algunos expedientes de ejemplo DELETE FROM expedientes WHERE code IN ('exp1','exp2', 'exp3'); INSERT INTO expedientes (code) VALUES ('exp1'); INSERT INTO expedientes (code) VALUES ('exp2'); INSERT INTO expedientes (code) VALUES ('exp3'); -- Si no existe la tabla de cambios de esstado la creamos SELECT COUNT(*) INTO cuenta FROM `information_schema`.`tables` WHERE TABLE_SCHEMA='db_test' AND TABLE_NAME='expStatusHistory' LIMIT 1; IF (cuenta = 0) THEN CREATE TABLE `expStatusHistory` ( `id` INT AUTO_INCREMENT, `code` VARCHAR(15) NOT NULL COMMENT 'Código del expediente', `state` VARCHAR(20) NOT NULL COMMENT 'Estado del expediente', `date` TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha/Hora en la que el expediente pasó a ese estado', PRIMARY KEY `PK_ExpHistory` (`id`) ) ENGINE=MyISAM CHARSET=utf8 collate=utf8_general_ci; -- No transacciones => MyISAM END IF; END; $$ -- Invocamos el procedimiento almacenado CALL db_test.procedureTemp(); $$ -- Borramos el procedimiento almacenado DROP PROCEDURE IF EXISTS db_test.procedureTemp; $$ -- Borramos el Trigger si existise DROP TRIGGER IF EXISTS StatusChangeDateTrigger; $$ -- Cremamos un Trigger sobre la tabla expedientes CREATE TRIGGER StatusChangeDateTrigger BEFORE UPDATE ON expedientes FOR EACH ROW BEGIN -- ¿Ha cambiado el estado? IF NEW.state != OLD.state THEN -- Actualizamos el campo stateChangedDate a la fecha/hora actual SET NEW.stateChangedDate = NOW(); -- A modo de auditoría, añadimos un registro en la tabla expStatusHistory INSERT INTO expStatusHistory (`code`, `state`) VALUES (NEW.code, NEW.state); END IF; END; $$ DELIMITER; |
Ejemplo en SQLServer.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 |
-- -------------------------------------------------------------------- -- Creamos una base de datos si no existiese. -- -------------------------------------------------------------------- IF NOT EXISTS (SELECT * from sys.databases where name = 'db_test') BEGIN CREATE DATABASE db_test; END -- Establecemos la base de datos predeterminada USE db_test; -- -------------------------------------------------------------------- -- Creamos una tabla si no existiese. -- Representa los datos de expedientes -- -------------------------------------------------------------------- IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE name='expedientes' AND xtype='U') BEGIN CREATE TABLE expedientes ( code VARCHAR(15) NOT NULL, state VARCHAR(20) DEFAULT 'INICIO', stateChangedDate DATETIME, PRIMARY KEY (code) ); END; -- Insertamos algunos expedientes de ejemplo DELETE FROM expedientes WHERE code IN ('exp1','exp2', 'exp3'); INSERT INTO expedientes (code) VALUES ('exp1'); INSERT INTO expedientes (code) VALUES ('exp2'); INSERT INTO expedientes (code) VALUES ('exp3'); -- Si no existe la tabla de cambios de esstado la creamos IF NOT EXISTS (SELECT * FROM sys.sysobjects WHERE name='expStatusHistory' AND xtype='U') BEGIN CREATE TABLE expStatusHistory ( id INT IDENTITY, code VARCHAR(15) NOT NULL, state VARCHAR(20) NOT NULL, date DATETIME DEFAULT GetDate(), PRIMARY KEY (id) ); END; -- Borramos el Trigger si existise IF OBJECT_ID ('StatusChangeDateTrigger', 'TR') IS NOT NULL BEGIN DROP TRIGGER StatusChangeDateTrigger; END; GO -- Necesario -- Cremamos un Trigger sobre la tabla expedientes CREATE TRIGGER StatusChangeDateTrigger ON expedientes AFTER UPDATE AS -- ¿Ha cambiado el estado? IF UPDATE(state) BEGIN -- Actualizamos el campo stateChangedDate a la fecha/hora actual UPDATE expedientes SET stateChangedDate=GetDate() WHERE code=(SELECT code FROM inserted); -- A modo de auditoría, añadimos un registro en la tabla expStatusHistory INSERT INTO expStatusHistory (code, state) (SELECT code, state FROM deleted WHERE code=deleted.code); -- La tabla deleted contiene información sobre los valores ANTIGUOS mientras que la tabla inserted contiene los NUEVOS valores. -- Ambas tablas son virtuales y tienen la misma estructura que la tabla a la que se asocia el Trigger. END; |
Si ahora modificasemos un registro a través de una sentencia como la siguiente el Trigger sería ejecutado y realizaría su trabajo.
|
1 |
UPDATE expedientes SET state='PENDIENTE_COBRO' WHERE code='exp1' |
Cabe destacar que en este tema se puede ir mucho más allá, pero bueno a modo didáctico y recordatorio están bastante bien artículos de este tipo.
Referencias
- CREATE TRIGGER (MySQL)
- CREATE TRIGGER (SQLServer)
- CREATE TRIGGER (Oracle)
Despedida
Bueno, espero que os haya servido de utilidad este pequeño tutorial, yo personalmente lo usaré a modo de consulta, pues hay veces que uno se tira mucho tiempo en otros entornos y luego vienen bien estos tutoriales para refrescar la memoria ;-).
Carlos García Pérez. Creador de MobileTest, un complemento educativo para los profesores y sus alumnos.
cgpcosmad@gmail.com
Saludos,
Actualmente hay un ERP funcionando en la empresa para ser mas exacto es un Jedward y del cual necesito extraer y luego colocar datos desde sqlserver. Me podrian dar una idea de por donde empezar por favor?… En el JD estan los datos y lo que se desea es hacer una interface de conexion para poder extarer y luego colocar datos.
Agrdezco de antemano su ayuda.
Hola buenas tardes.
¿Ustedes pueden ayudarme para saber como crear un trigger de oracle?
tengo varios ejemplos que he generado pero ninguno me ha servido.
En el caso de que sea afirmativa su respuesta, pueden avisarme por favor para enviarle mis ejemplos.
Saludos y gracias.
Buenos dias como puedo hacer que un trigger me devuelva un valor para saber si en mi formulario hubo cambios ejecutar la sentencia update en caso que no haya cambios..
intente hacer con este me funciona
USE
db_RR_HH;DELIMITER $$
CREATE DEFINER=
root@localhostTRIGGER verif_cambios_personasBEFORE UPDATE ON personas
FOR EACH ROW
BEGIN
— ¿Ha cambiado el estado?
IF NEW.Nombre = OLD.Nombre ||
NEW.SegundoNombre = OLD.SegundoNombre ||
NEW.Apellido = OLD.Apellido ||
NEW.SegundoApellido = OLD.SegundoApellido ||
NEW.CI = OLD.CI ||
NEW.FechaNac = OLD.FechaNac ||
NEW.FechaNac = OLD.FechaNac ||
NEW.FechaNac = OLD.FechaNac ||
NEW.FechaNac = OLD.FechaNac ||
NEW.FechaNac = OLD.FechaNac ||
NEW.FechaNac = OLD.FechaNac ||
NEW.FechaNac = OLD.FechaNac
then
SIGNAL sqlstate ‘45001’ set message_text = «No way ! You cannot do this !»;
end if;
END
pero lo que necesito es solo que me mande un valor para que por ejemplo si no hubo cambios no ejecute la sentencia update y solo me habilite los botones de Telefonos de Contacto y
Direcciones, y si hubo cambios que actualice los datos y luego habilite esos Botones intente tambien hacer con programacion pero aun no me sale ..
Hoy me podran ayudar a crear un trigger que llenar cuadros de texto con selecciones de cuadro combinado de múltiples columnas.
HOLA
NECESITO SABER SI ME PUEDEN AYUDAR A CREAR Y/O DEFINIR UN TRIGER QUE CUANDO EL ERP PREGUNTE SOBRE UNA TABLA DETERMINADA CUANTOS USUARIOS HAY ACTIVOS SIEMPRE RESPONDA EL VALOR 1.
GRACIAS.
mi mail es: mcc_1962@hotmail.com
podria ayudarme con una duda con el trigger «SE PUEDE HACERLE SUMAS O RESTAS A UN CAMPO DE UNA TABLA»????????????????????????????????