Debers

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 deescritorio 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 datosque ejecutan acciones cuando se producen ciertoseventos (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:
1. Anotará en el campo "stateChangedDate" la fecha/hora en la que se produjo un cambio de estado.
2. 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 autocomentado en MySql
* Ejemplo autocomentado en SQLServer
Ejemploen MySql.
view plainprint?
1. DELIMITER $$  
2.   
3. USE db_test;  
4.   
5. $$  
6.   
7. # Creamos el Schema si no existe  
8. CREATE SCHEMA IF NOT EXISTS db_test;  
9.   
10. $$  
11.   
12. -- Eliminamos el procedimiento almancenado si existise  
13. DROP PROCEDURE IF EXISTS db_test.procedureTemp;  
14.   
15. $$  
16.   17. CREATE PROCEDURE db_test.procedureTemp()  
18. BEGIN  
19.   DECLARE cuenta  INT DEFAULT 0;  
20.   
21.   -- Si no existe la tabla de expedientes, la creamos.  
22.   SELECT COUNT(*) INTO cuenta FROM `information_schema`.`tables` WHERE TABLE_SCHEMA='db_test' AND TABLE_NAME='expedientes' LIMIT 1;  
23.   IF (cuenta = 0)  THEN  
24.     CREATE TABLE `expedientes` (  25.       code             VARCHAR(15)  NOT NULL COMMENT 'Código del expediente',  
26.       state            VARCHAR(20)  COMMENT 'Estado del expediente',  
27.       stateChangedDate DATETIME     COMMENT 'Fecha/Hora en la que se produció el último cambio de estado',  
28.   
29.       PRIMARY KEY `PK_Exp` (code)  
30.    ) ENGINE=InnoDB CHARSET=utf8 collate=utf8_general_ci;  
31.   END IF;  
32.   
33.   -- Insertamos algunos expedientes de ejemplo  
34.   DELETE FROM expedientes WHERE code IN ('exp1','exp2', 'exp3');  
35.   INSERT INTO expedientes (code) VALUES ('exp1');  
36.   INSERT INTO expedientes (code) VALUES ('exp2');  
37.   INSERT INTO expedientes (code) VALUES ('exp3');  
38.   
39.   
40.   
41.  -- Si no existe la tabla de cambios de esstado la creamos  
42.   SELECT COUNT(*) INTO cuenta FROM `information_schema`.`tables` WHERE TABLE_SCHEMA='db_test' AND TABLE_NAME='expStatusHistory' LIMIT 1;  
43.   IF (cuenta = 0)  THEN  
44.     CREATE TABLE `expStatusHistory` (  
45.       `id`    INT         AUTO_INCREMENT,  
46.       `code`  VARCHAR(15) NOT NULL COMMENT 'Código del expediente',  47.       `state` VARCHAR(20) NOT NULL COMMENT 'Estado del expediente',  
48.       `date`  TIMESTAMP   DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha/Hora en la que el expediente pasó a ese estado',  
49.       PRIMARY KEY `PK_ExpHistory` (`id`)  
50.     ) ENGINE=MyISAM CHARSET=utf8 collate=utf8_general_ci;  -- No transacciones => MyISAM  
51.   END IF;  
52....