Ing. BETTY SUÁREZ TORRES

TRIGGERS O DESENCADENADORES

2

CONCEPTOS BÁSICOS

Un disparador es un tipo especial de procedimiento almacenado

que se ejecuta cuando se insertan, eliminan o actualizan datos de

una tabla especificada.

Los disparadores pueden ayuda a mantener la integridad de

referencia de los datos conservando la consistencia entre los datos

relacionados lógicamente de distintas tablas.

La principal ventaja de los disparadores es que son automáticos:

funcionan cualquiera sea el origen de la modificación de los datos.

Cada disparador es específico de una o más operaciones de

modificación de datos, UPDATE, INSERT o DELETE. El disparador se

ejecuta una vez por cada instrucción.

3

SINTAXIS

CREATE TRIGGER [ esquema. ]nombre_triggerON { Tabla | Vista }{ FOR | AFTER | INSTEAD OF }{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }[ WITH APPEND ][ NOT FOR REPLICATION ]ASsentencia sql [ ; ]

4

TRIGGER CON DISPARADOR DE INSERCIÓN

Trigger que no permite eliminar más de un registro a la vez de la tabla proveedorescreate trigger tr_elimina_prov on proveedores for delete as if (select count(*) from deleted)>1 begin raiserror('No puede eliminar más de un 1 proveedor', 16, 1) rollback transaction end--delete from proveedores where idproveedor=4 or idproveedor=5

5

TRIGGER CON DISPARADOR DE ACTUALIZACIÓN

create trigger tr_insert_categoriason categoriasfor update

asif update(nombrecategoria) print 'columna nombre de categoría

modificada'

6

EJERCICIOS

Trigger que al hacer un pedido actualiza el total de la orden y el stock del producto (BD: Pedidos)

create trigger tr_detallepedidoon itemfor insertas update ordenpedido set total=item.subtotal+inserted.subtotal from ordenpedido, inserted, item where ordenpedido.ordid=inserted.ordid and ordenpedido.ordid=item.ordidupdate producto set stock=stock-inserted.cantidad from producto, inserted where producto.prodid=inserted.prodid

7

TRIGGERS CON INSTEAD OF

Los desencadenadores INSTEAD OF pasan por alto las acciones estándar de la instrucción de desencadenamiento: INSERT, UPDATE o DELETE. Se puede definir un desencadenador INSTEAD OF para realizar comprobación de errores o valores en una o más columnas y, a continuación, realizar acciones adicionales antes de insertar el registro.Por ejemplo, cuando el valor que se actualiza en un campo de tarifa de una hora de trabajo de una tabla de planilla excede un valor específico, se puede definir un desencadenador para producir un error y revertir la transacción o insertar un nuevo registro en un registro de auditoría antes de insertar el registro en la tabla de planilla.

8

EJEMPLO CON INSTEAD OF UPDATE

create trigger actualizaron proveedoresinstead of updateasif update (nomproveedor) begin

set nocount on; raiserror ('imposible actualizar los registros',16, 1)

rollback tranend--select * from proveedoresupdate proveedores set nomproveedor='aaa'where nomproveedor= 'TB EXPORT'

9

EJEMPLO CON INSTEAD OF INSERT

Trigger que en caso de inserción sobre la tabla proveedor_pais verifique la existencia del código de pais correspondiente y en caso de no encontrarla no realice el proceso.create trigger tr_inserta_pais on proveedor_paisinstead of insertasif exists(select idpais from paises where idpais=(select idpais from inserted))begininsert into proveedor_paisselect * from insertedendelse rollback transaction

create table proveedor_pais(idpais char(3) primary key,nombrepais varchar(40))--insert into proveedor_pais values('006','ewrewr‘)

10

EJERCICIOS PROPUESTOS

1. Crear un trigger sobre la tabla pais que en caso de que se borre una fila de ésta elimine las respectivas filas de la tabla proveedor

2. Trigger que en caso de inserción sobre la tabla producto verifique la existencia del código de categoría y del código del proveedor y en caso de no encontrarlos no realice el proceso.

3. Creamos un disparador para que no permita eliminar más de un registro a la vez de la tabla categorías

4. Creamos un disparador para que no permita ingresar productos de la categoría ‘bebidas’

5. Al insertar un producto verificar que el stock no sea negativo6. Crear un trigger de actualización que muestre el valor anterior y el

nuevo valor de los registros actualizados en la tabla producto7. Crear un trigger que se active cuando eliminamos un pedido elimine

los pedidos en el detalle