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:
- 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;
