TALLER DE BASE DE DATOS

Moises Huarocc Huamaní

TRIGGER

TRIGGER O DISPARADORES

• UN DISPARADOR O TRIGGER es un procedimiento que

se ejecuta cuando se cumple una condición establecida

al realizar una operación.

• Es una rutina autónoma asociada con una tabla o vista

que automáticamente realiza una acción cuando una fila

en la tabla o la vista se inserta (INSERT), se actualiza

(UPDATE), o borra (DELETE). Un Disparador nunca se

llama directamente, en cambio, cuando una aplicación o

usuario intenta insertar, actualizar, o anular una fila en

una tabla, la acción definida en el disparador se ejecuta

automáticamente (se dispara).

USOS

• Son usados para mejorar la administración de la Base de

datos, sin necesidad de contar con que el usuario ejecute

la sentencia de SQL.

• Además, pueden generar valores de columnas, previene

errores de datos, sincroniza tablas, modifica valores de

una vista, etc.

• Permite implementar programas basados en paradigma

lógico (sistemas expertos, deducción).

VENTAJAS

• El mantenimiento de la aplicación se reduce, los cambios

a un disparador se refleja automáticamente en todas las

aplicaciones que tienen que ver con la tabla sin la

necesidad de recompilar.

• Los Disparadores tienen dos palabras clave, OLD y NEW

que se refieren a los valores que tienen las columnas

antes y después de la modificación. Los INSERT

permiten NEW, los DELETE sólo OLD y los UPDATE

ambas.

SINTAXIS GENERAL DE UN DISPARADOR

CREATE [OR REPLACE] TRIGGER nombre

[temporalidad del evento]

[granularidad del evento]

[WHEN condición]

BEGIN

cuerpo del trigger

END nombre;

SINTAXIS GENERAL DE UN DISPARADOR

• CREATE [OR REPLACE] TRIGGER nombre

• Crea o reemplaza un disparador con el nombre

especificado.

• [temporalidad del evento]

• Puede tomar dos valores: BEFORE ó AFTER que

indicará si el cuerpo del disparador

• debe ejecutarse antes o después del evento que causa la

activación del disparador.

• Ambos valores pueden aplicarse tanto para disparadores

a nivel de fila como a nivel de

• orden.

SINTAXIS GENERAL DE UN DISPARADOR

• La opción BEFORE/AFTER debe acompañarse de la

operación que causa la activación del disparador.

• Estas pueden ser operaciones de inserción (INSERT)

Y/O borrado (DELETE) y/o modificación (UPDATE)

respecto a una tabla o respecto a una columna de una

tabla. Cuando se quiere especificar más operación, estas

se pueden unir utilizando los operadores OR y AND.

SINTAXIS GENERAL DE UN DISPARADOR

• [ granularidad del evento]

• [WHEN condición]

• Permite distinguir si el disparador es a nivel de fila o a nivel de orden.

• Un disparador se define a nivel de fila cuando el cuerpo del disparador se debe aplicar fila a fila de la tabla afectada y se define a nivel de orden cuando se debe aplicar a toda la tabla a la vez.

• Si queremos definir el disparador a nivel de fila, la granularidad deberá especificarse con la cláusula FOR EACH ROW a la que habrá que acompañar de la condición que debe cumplir la fila para aplicar el cuerpo del disparador.

SINTAXIS GENERAL DE UN DISPARADOR

• Esta condición se indica en la orden SQL mediante la

cláusula WHEN condición.

• Cuando no exista definición del granularidad del evento,

significará que el disparador está definido a nivel de

orden.

EJEMPLOS CREATE TABLE test1(a1 INT); CREATE TABLE test2(a2 INT); CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY); CREATE TABLE test4( a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY, b4 INT DEFAULT 0 ); DELIMITER | CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2 = NEW.a1; DELETE FROM test3 WHERE a3 = NEW.a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1; END | DELIMITER ; INSERT INTO test3 (a3) VALUES (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL), (NULL); INSERT INTO test4 (a4) VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0); INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);

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