Triggers o Disparadores SQL

6
291568

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:

  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 en MySql.

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.

-- --------------------------------------------------------------------
-- 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.

    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

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

6 COMENTARIOS

  1. 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.

  2. 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.

  3. 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`@`localhost` TRIGGER verif_cambios_personas
    BEFORE 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 ..

DEJA UNA RESPUESTA

Por favor ingrese su comentario!

He leído y acepto la política de privacidad

Por favor ingrese su nombre aquí

Información básica acerca de la protección de datos

  • Responsable:
  • Finalidad:
  • Legitimación:
  • Destinatarios:
  • Derechos:
  • Más información: Puedes ampliar información acerca de la protección de datos en el siguiente enlace:política de privacidad