Disparadores sql

DISPARADORES EN SQL (I)

MODELOS AVANZADOS DE BASES DE DATOS

DISPARADORES EN SQL (I)

Objetivo Conocer la implementación de los disparadores en SQL.

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

CREATE [OR REPLACE] TRIGGER nombre Crea o reemplaza undisparador 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. La opción BEFORE/AFTER debe acompañarse de la operación que causa la activacióndel 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. La forma general de la temporalidad del evento será, por tanto: BEFORE/AFTER nombre_tabla Por ejemplo: BEFORE INSERT ONempleado BEFORE INSERT OR DELETE ON empleado BEFORE UPDATE ON salario ON empleado INSERT/UPDATE/DELETE [OF nombre_columna] ON

MODELOS AVANZADOS DE BASES DE DATOS Coral Calero

2

DISPARADORES EN SQL (I)

Esta última se activará para una operación como: UPDATE empleado SET salario=salario+1; Y también para: UPDATE empleado SET salario=salario; Pero no para: UPDATE empleado SET emp_cod=101; Lacláusula OF nombre_columna puede utilizarse tanto a nivel de fila como a nivel de orden.

[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 ala 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. 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 disparadorestá definido a nivel de orden. Ejemplos: CREATE TRIGGER nivel_fila_sin_condición AFTER DELETE OF salario ON empleado FOR EACH ROW BEGIN ... CREATE TRIGGER nivel_fila_con_condición AFTER DELETE OF salario ON empleado FOR EACH ROW WHEN empleado.salario >60000 BEGIN ...

MODELOS AVANZADOS DE BASES DE DATOS Coral Calero

3

DISPARADORES EN SQL (I)

CREATE TRIGGER nivel_columna AFTER DELETE OFsalario ON empleado BEGIN ... Si se quiere hacer mención en la condición WHEN al valor de la fila a borrar, se debe hacer referencia mediante las variables NEW y OLD. Así, para el ejemplo anterior, el disparador estaría correctamente definido de la siguiente manera: CREATE TRIGGER nivel_fila_con_condición AFTER DELETE OF salario ON empleado FOR EACH ROW WHEN OLD.salario >60000 BEGIN ... ConOLD.nombre_columna referenciamos: • • • al valor que tenía la columna antes del cambio debido a una modificación (UPDATE) al valor de una columna antes de una operación de borrado sonbre la misma (DELETE) al valor NULL para operaciones de inserción (INSERT)

Con NEW.nombre_columna referenciamos: • • • Al valor de una nueva columna después de una operación de inserción (INSERT) Al valor de una columnadespués de modificarla mediante una sentencia de modificación (UPDATE) Al valor NULL para una operación de borrado (DELETE)

Sin embargo hay que tener en cuenta que las variable OLD y NEW sólo pueden ser utilizadas cuando el disparador se ha definido a nivel de fila.

BEGIN cuerpo del trigger END nombre; Dentro del cuerpo de un disparador podemos incluir sentencias de borrado (DELETE),...