Trigger sql

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

Ejemplo SQL

-- --------------------------------------------------------------------

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