Conceptos de Bases de Datos Relacionales Triggers

Trigger • Un trigger es un procedimiento almacenado asociado con una tabla, el cual se ejecuta automáticamente cuando se modifica un dato de esa tabla Llamado explícito

Ejecución automática

Uso de parámetros

Stored Procedure definido por el usuario

SI

No

SI

Trigger

No

SI

No

Aplicaciones Típicas de triggers • Hacer modificaciones en cascada sobre tablas relacionadas • Deshacer cambios que violan la integridad de los datos • Forzar restricciones que son muy complejas para reglas y restricciones • Mantener datos duplicados • Mantener columnas con datos derivados • Hacer ajustes de registros

Definición de un trigger • Un trigger se define asociado con una tabla para una o más sentencias de manipulación de datos – Un trigger se puede definir para insert, update, o delete o cualquier combinación de ellos

Activación de un trigger • Cuando se modifica un dato en una tabla que tiene declarado un trigger para esa sentencia, el trigger se “dispara” – El trigger se dispara una vez, independientemente del número de filas afectadas – El trigger se dispara aunque no hayan filas afectadas

Triggers and transacciones • Un trigger es parte de la transacción que causa el disparo • El trigger puede deshacer: – Así mismo, solamente – Así mismo y la sentencia que causó el disparo – La transacción total

Reglas para triggers • Los triggers pueden: – Declarar variables locales – Invocar procedimientos almacenados

• Los triggers no pueden: – – – –

Llamarse directamente Usar parámetros Definirse sobre tablas temporales o vistas Crear objetos permanentes de base de datos

• Las operaciones con registro mínimo (como select into) no disparan los triggers

Crear triggers • Sintaxis simplificada: create trigger nombre_del_trigger on nombre_de_la_tabla for {insert | update | delete} [, {insert | update | delete} ...] as sentencias_sql EJEMPLO: CREATE TABLE EJ_TRIGGER ( A INT PRIMARY KEY, B CHAR (30)) CREATE TRIGGER TR_I_EJ_TRIGGER ON EJ_TRIGGER FOR INSERT AS IF datename(month, getdate()) = "October" BEGIN SELECT "En Octubre no hay inserciones" ROLLBACK TRIGGER END INSERT INTO EJ_TRIGGER VALUES (2, 'Dos') SELECT * FROM EJ_TRIGGER

Borrar Triggers • Sintaxis simplificada: drop trigger nombre_del_trigger

Procedimientos del sistema para procedimientos almacenados • sp_depends {nombre_de_tabla | nombre_del_trigger} – Con nombre_de_tabla, lista todos los objetos (incluyendo triggers) de la misma base de dtos – Con nombre_del_trigger, lista todas las tablas referenciadas

• sp_help nombre_del_trigger – Muestra información del trigger

• sp_helptext nombre_del_trigger – Muestra el código usado para crear el trigger

• sp_rename nombre_anterior_del_trigger, nombre_nuevo_del_trigger – Cambia el nombre del trigger

Triggers - ejemplo • Crear dos tablas:

CREATE TABLE EJ_TRIGGER_2A ( A INT PRIMARY KEY, B CHAR (30)) CREATE TABLE EJ_TRIGGER_2B ( ID NUMERIC IDENTITY PRIMARY KEY, FECHA DATETIME, FILAS INT)

• Crear un trigger que guarde la fecha y número de filas afectadas por cada delete: CREATE TRIGGER TR_D_EJ_TRIGGER_2 ON EJ_TRIGGER_2A FOR DELETE AS INSERT INTO EJ_TRIGGER_2B VALUES (getdate(), @@rowcount) RETURN

Triggers - ejemplo • Ejecutar los siguientes insert

INSERT INTO EJ_TRIGGER_2A VALUES (5, 'Cinco') INSERT INTO EJ_TRIGGER_2A VALUES (2, 'Dos') INSERT INTO EJ_TRIGGER_2A VALUES (7, 'Siete')

• Ejecutar un delete y ver las tablas

EJ_TRIGGER_2A y EJ_TRIGGER_2B:

DELETE FROM EJ_TRIGGER_2A WHERE A > 4 SELECT * FROM EJ_TRIGGER_2A A B ----------- -----------------------------2 Dos SELECT * FROM EJ_TRIGGER_2B ID FECHA FILAS -------------------- ------------------------- ----------1 2010-10-19 02:00:13.533 2

• Ejecutar un delete que no afecta filas y ver la tabla EJ_TRIGGER_2B: DELETE FROM EJ_TRIGGER_2A WHERE A > 8 SELECT * FROM EJ_TRIGGER_2B ID FECHA -------------------- ------------------------1 2010-10-19 02:00:13.533 2 2010-10-19 02:21:43.153

FILAS ----------2 0

Las tablas inserted y deleted • inserted y deleted son dos tablas que se crean automáticamente cada vez que se dispara un trigger – inserted almacena cualquier fila que se vaya a añadir a la tabla – deleted almacena cualquier fila que se vaya a borrar de la tabla

Borrados • A delete adds rows to the deleted table

Inserciones • An insert adds rows to the inserted table

Actualizaciones • An update adds rows to both tables

Reglas para las tablas inserted y deleted • Ambas tablas tienen las mismas columnas que la tabla asociada al trigger • El trigger puede consultar datos de las dos tablas – Otros procesos no pueden consultar datos de las dos tablas

• El trigger no puede modificar datos en las dos tablas • Cada anidamiento de triggers tiene sus propias tablas inserted y deleted – Si un trigger modifica datos de su tabla asociada, esos cambios no se reflejan en las tablas inserted and deleted de ese trigger

Triggers y rollbacks • Tres tipos de rollbacks: – Deshacer el trigger – Deshacer el trigger y la sentencia que lo disparó – Deshacer toda la transacción

Deshacer un trigger • Para deshacer un trigger, declarar un punto de grabación y luego hacer el rollback – Un rollback sin punto de grabación deshace toda la transacción Procedimiento almacenado Trigger Caso A

begin tran ... insert ... print "in sp" ... commit tran print "sp done"

save tran s1 .... rollback tran s1 print “tr done” return

Procedimiento almacenado Caso B

(este caso ocaciona un error)

begin tran ... insert ... print "in sp" ... commit tran print "sp done"

Trigger

begin tran s2 .... rollback tran s2 print “tr done” return

Deshacer un trigger • rollback trigger deshace el trigger y la sentencia que lo disparó • Sintaxis: rollback trigger [with raiserror error_number [error_statement] ]

• Ejemplo:

create trigger trg_i_publishers on publishers for insert as if @@rowcount > 1 begin rollback trigger with raiserror 40031 "You cannot insert more than one publisher at a time." return end

Deshacer un trigger Procedimiento almacenado Caso C begin tran ... insert ... print "in sp" ... commit tran print "sp done"

Trigger .... .... rollback trigger print “tr done” return

Deshacer una transacción • Para deshacer toda la transacción donde está inmerso el trigger, ejecutar un rollback sin un punto de grabación Procedimiento almacenado Trigger Caso D begin tran ... insert ... print "in sp" ... commit tran print "sp done"

Procedimiento almacenado Caso E begin tran ... insert ... print "in sp" ... commit tran print "sp done"

begin tran ... rollback tran print "tr done” return

Trigger

.... .... Rollback tran print “tr done” return

Prácticas recomendadas al hacer Triggers • Consideraciones al elaborar triggers al usar: – – – –

@@rowcount if update triggers anidados triggers recursivos

if update • if update es una condición que le permite a un trigger chequear si ha habido un cambio en una determinada columna • Sólo se puede usar en triggers • Usualmente se usa para chequear si el valor de una llave primaria ha cambiado • Sintaxis simplificada:

if update (column_name) [ {and | or} update (column_name)]...

Triggers anidados • Un trigger anidado es un trigger que se dispara en respuesta a una modificación hecha en un trigger

• Nivel máximo de anidamiento: 16 – Tanto los procedimientos almacenados como los triggers cuentan en la determinación del nivel máximo – @@nestlevel retorna el nivel de anidamiento

Triggers recursivos • Un trigger recursivo es aquel que se dispara cuando modifica su propia tabla

• Por default, un trigger que modifica su propia tabla no causa un disparo recursivo del trigger

Métodos para integridad de datos • Métodos para implementar integridad de datos Domain Integrity

Entity Integrity

Referential Integrity

Constraints

Check constraints

Primary key constraints, unique constraints

References constraints

Database Objects

Rules

Indexes

Triggers

Actualización de valores llave Acción deseada

Restricciones

Triggers

Insertarar valor de llave primaria

Permitido

Permitido

Insertar valor de llave foránea

Permitido

Permitido

Actualizar valor de llave primaria

No permitido*

Permitido

Actualizar valor de llave foránea

Permitido

Permitido

Borrar valor de llave primaria

No permitido*

Permitido

Borrar valor de llave foránea

Permitido

Permitido

*Valores de llaves primarias se pueden actualizar o borrar si no están referencidos en llaves foráneas

• Solamente en triggers es posible borrar o actualizar una llave primaria • Sólo en triggers es posible hacer cambios en cascada

Restricciones vs triggers • Ventajas de las restricciones: – Las restricciones (y reglas) son más rápidas que los triggers – Las restricciones no requieren codificación adicional – Es mejor para chequear datos antes de ingresarlos a la base de datos

• Ventajas de los triggers: – Muy flexible • Los triggers pueden hacer cualquier cosa que se pueda codificar

– Mejor para las reglas complejas del negocio que no se pueden expresar como restricciones referenciales tales como actualizaciones o borrados en cascada