Procedimientos almacenados y disparadores

Un procedimiento es un subprograma que ejecuta una acción específica y que no devuelve ningún valor. Un procedimiento tiene un nombre, un conjunto de parámetros (opcional) y un bloque de código.

CREATE
PROCEDURE <nombre_procedure> [(<param1> [IN|OUT|IN OUT] <type>,
<param2> [IN|OUT|IN OUT] <type>, ...)]
BEGIN
-- Sentencias
END ;

Al especificar el tipo de dato del parámetro no debemos especificar la longitud del tipo.

Los parámetros pueden ser de entrada (IN), de salida (OUT) o de entrada salida (IN OUT). El valor por defecto es IN, y se toma ese valor en caso de que no especifiquemos nada.

Un disparador (o trigger) es un tipo especial de procedimiento almacenado asociado a una tabla que se ejecuta al realizar una operación “básica” (INSERT, un DELETE o un UPDATE) sobre ésta. La operación básica que despierta al trigger es conocida como sentencia disparadora.

La ejecución del disparador puede ser antes (before) o después (after) de llevar a cabo la sentencia disparadora.

Para diseñar un disparador hay que cumplir dos requisitos:

Especificar las condiciones en las que se va a ejecutar el disparador.
Especificar las acciones que se van a realizar cuando se ejecute el disparador.

Los disparadores sea activan al crearlos.

Eliminar un disparador: DROP TRIGGER nombre_disparador;

Activar/ Desactivar dispadores: Existen dos opciones.

ALTER TRIGGER nombre_disparador {DISABLE | ENABLE};
ALTER TABLE nombre_tabla {ENABLE | DISABLE} ALL TRIGGERS;

ejemplo:

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.   
53. END;  
54.   
55. $$  
56.   
57. -- Invocamos el procedimiento almacenado  
58. CALL db_test.procedureTemp();  
59.   
60. $$  
61. -- Borramos el procedimiento almacenado  
62. DROP PROCEDURE IF EXISTS db_test.procedureTemp;  
63.   
64. $$  
65.   
66. -- Borramos el Trigger si existise  
67. DROP TRIGGER IF EXISTS StatusChangeDateTrigger;  
68.   
69. $$  
70.   
71. -- Cremamos un Trigger sobre la tabla expedientes  
72.   
73. CREATE TRIGGER StatusChangeDateTrigger  
74.     BEFORE UPDATE ON expedientes FOR EACH ROW  
75.     BEGIN  
76.          -- ¿Ha cambiado el estado?  
77.          IF NEW.state != OLD.state THEN  
78.             -- Actualizamos el campo stateChangedDate a la fecha/hora actual  
79.             SET NEW.stateChangedDate = NOW();  
80.   
81.             -- A modo de auditoría, añadimos un registro en la tabla expStatusHistory  
82.             INSERT INTO expStatusHistory (`code`, `state`) VALUES (NEW.code, NEW.state);  
83.          END IF;  
84.     END;  
85.   
86. $$  
87.   
88. DELIMITER;