Bases de Datos Relacionales
Preparó: Ismael Castañeda FuentesFuentes: Manuales Sybase
Manuales SQL ServerManuales Oracle
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
¿Se puede llamar explícita-mente?
¿Se puede ejecutar automática-mente?
¿Puede usar parámetros?
Procedimiento definido por el usuario
Sí No Sí
Trigger No Sí No
Trigger - Aplicaciones Típicas
• Hacer modificarciones 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
Trigger - Definición
• 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
Trigger - Activación
• 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 causa el disparo– La transacción total
Trigger - Reglas
• 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
Trigger - Crear
• Sintaxis simplificada:
create trigger trigger_name
on table_name
for {insert | update | delete} [, {insert | update | delete} ...]
as
sql_statements
Borrar Triggers
• Sintaxis simplificada:drop trigger trigger_name
Procedimientos del sistema para Triggers
• sp_depends {table_name | trigger_name}– Cuando se da el nombre de tabla, lista todos los objetos (incluyendo triggers) de la
misma base de dtos– Cuando se da el nombre de trigger, lista todas las tablas referencias
• sp_help trigger_name– Muestra información del trigger
• sp_helptext trigger_name– Muestra el código usado para crear el trigger
• sp_rename old_trigger_name, new_trigger_name– Cambia el nombre del trigger
Trigger - 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
Trigger - Borrados
• A delete adds rows to the deleted table
Trigger - Inserciones
• insert añade filas en la tabla inserted
Trigger - Actualizaciones
Un update añade filas en ambas tablas
Trigger - Reglas para 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 almacenadoCaso A begin tran
...insert ...print "in sp"...commit tranprint "sp done"
Triggersave tran s1....rollback tran s1print “tr done”return
Procedimiento almacenadoCaso B begin tran
...(este caso insert ...ocaciona un print "in sp"error) ...
commit tranprint "sp done"
Trigger
begin tran s2....rollback tran s2print “tr done”return
• rollback trigger deshace el trigger y la sentencia que lo disparó
• Sintaxis:rollback trigger [with raiserror error_number [error_statement] ]
Deshacer un trigger
Procedimiento almacenado
Caso C begin tran...insert ...print "in sp"...commit tranprint "sp done"
Trigger........rollback triggerprint “tr done”return
Procedimiento almacenadoCase D begin tran
...insert ...print "in sp"...commit tranprint "sp done"
Triggerbegin tran...rollback tranprint "tr done”return
Para deshacer toda la transacción donde está inmerso el trigger, ejecutar un rollback sin un punto de grabación
Procedimiento almacenadoCase E begin tran
...insert ...print "in sp"...commit tranprint "sp done"
Trigger........Rollback tranprint “tr done”return
Deshacer una transacción
Trigger - Prácticas recomendadas
• Consideraciones al elaborar triggers:– @@rowcount– if update– triggers anidados – triggers recursivos
Trigger - 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
Dos métodos para implementar integridad de datos
Integridad a nivel de Dominio
Integridad a nivel de Entidad
Integridad Referencial
Restricciones
Checks
Primary key, restricciones unique
Referencias
Objetos de base de datos
Reglas Indices Triggers
Actualización de valores llave
• Solamente en triggers es posible borrar o actualizar una llave primaria• Sólo en triggers es posible hacer cambios en cascada
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
Restricciones versus 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