PDF de programación - Triggers sql server

Triggers

También llamados disparadores o desencadenadores, es un tipo de procedimiento almacenado
que se ejecuta cuando se intenta modificar los datos de una tabla (o vista).

Se definen para una tabla (o vista) específica.

Se crean para conservar la integridad referencial y la coherencia entre los datos entre distintas
tablas.

Si se intenta modificar (agregar, actualizar o eliminar) datos de una tabla en la que se definió un
disparador para alguna de estas acciones (inserción, actualización y eliminación), el disparador se
ejecuta (se dispara) en forma automática.

Un trigger se asocia a un evento (inserción, actualización o borrado) sobre una tabla.

La diferencia con los procedimientos almacenados del sistema es que los triggers:

- no pueden ser invocados directamente; al intentar modificar los datos de una tabla para la que
se ha definido un disparador, el disparador se ejecuta automáticamente.

- no reciben y retornan parámetros.

- son apropiados para mantener la integridad de los datos, no para obtener resultados de
consultas.

Los disparadores, a diferencia de las restricciones "check", pueden hacer referencia a campos de
otras tablas. Por ejemplo, puede crearse un trigger de inserción en la tabla "ventas" que
compruebe el campo "stock" de un artículo en la tabla "articulos"; el disparador controlaría que,
cuando el valor de "stock" sea menor a la cantidad que se intenta vender, la inserción del nuevo
registro en "ventas" no se realice.

Los disparadores se ejecutan DESPUES de la ejecución de una instrucción "insert", "update" o
"delete" en la tabla en la que fueron definidos. Las restricciones se comprueban ANTES de la
ejecución de una instrucción "insert", "update" o "delete". Por lo tanto, las restricciones se
comprueban primero, si se infringe alguna restricción, el desencadenador no llega a ejecutarse.

Los triggers se crean con la instrucción "create trigger". Esta instrucción especifica la tabla en la
que se define el disparador, los eventos para los que se ejecuta y las instrucciones que contiene.

Sintaxis básica:

create trigger NOMBREDISPARADOR

on NOMBRETABLA

for EVENTO- insert, update o delete

as

SENTENCIAS



Análisis de la sintaxis:

- "create trigger" junto al nombre del disparador.

- "on" seguido del nombre de la tabla o vista para la cual se establece el trigger.

- luego de "for", se indica la acción (evento, el tipo de modificación) sobre la tabla o vista que
activará el trigger. Puede ser "insert", "update" o "delete". Debe colocarse al menos UNA acción, si
se coloca más de una, deben separarse con comas.

- luego de "as" viene el cuerpo del trigger, se especifican las condiciones y acciones del disparador;
es decir, las condiciones que determinan cuando un intento de inserción, actualización o borrado
provoca las acciones que el trigger realizará.



Consideraciones generales:

- "create trigger" debe ser la primera sentencia de un bloque y sólo se puede aplicar a una tabla.

- un disparador se crea solamente en la base de datos actual pero puede hacer referencia a
objetos de otra base de datos.

- Las siguientes instrucciones no están permitidas en un desencadenador: create database, alter
database, drop database, load database, restore database, load log, reconfigure, restore log, disk
init, disk resize.

- Se pueden crear varios triggers para cada evento, es decir, para cada tipo de modificación
(inserción, actualización o borrado) para una misma tabla. Por ejemplo, se puede crear un "insert
trigger" para una tabla que ya tiene otro "insert trigger".

Ejemplo de un disparador de inserción.

Sintaxis básica:

create trigger NOMBREDISPARADOR
on NOMBRETABLA
for insert
as
SENTENCIAS


Para el ejemplo del trigger crear una Base de Datos que se llama ejemploTrigger1

Ejecutar las siguientes instrucciones para crear las tablas necesarias

create table libros(
codigo int identity,
titulo varchar(40),
autor varchar(30),
editorial varchar(20),
precio decimal(5,2),
stock int,
primary key(codigo)
);

insert into libros values('Uno','Richard Bach','Planeta',15,10);
insert into libros values('Ilusiones','Richard Bach','Planeta',18,9);
insert into libros values('El aleph','Borges','Emece',25,8);
insert into libros values('Aprenda PHP','Mario Molina','Nuevo siglo',45,7);
insert into libros values('Matematica estas ahi','Paenza','Nuevo siglo',12,6);
insert into libros values('Java en 10 minutos','Mario Molina','Paidos',35,5);

create table ventas(
codigolibro int,
cantidad int
);

Creamos un trigger sobre la tabla "ventas" para el evento se inserción. Cada vez que se realiza un
"insert" sobre "ventas", el disparador se ejecuta. El disparador controla que la cantidad que se
intenta vender sea menor o igual al stock del libro y actualiza el campo "stock" de "libros",
restando al valor anterior la cantidad vendida:

create trigger DIS_ventas_insertar
on ventas
for insert
as
declare @stock int
select @stock= stock from libros



if (@stock>=(select cantidad from inserted))
update libros set stock=stock-inserted.cantidad
from libros
join inserted
on inserted.codigolibro=libros.codigo
where codigo=inserted.codigolibro
else
begin

join inserted
on inserted.codigolibro=libros.codigo
where libros.codigo=inserted.codigolibro





raiserror ('Hay menos libros en stock de los solicitados para la venta', 16, 1)
rollback transaction
end

Entonces, creamos el disparador ("create trigger") dándole un nombre ("DI_ventas_insertar")
sobre ("on") una tabla específica ("ventas") para ("for") el suceso de inserción ("insert"). Luego se
"as" colocamos las sentencias, las acciones que el trigger realizará cuando se ingrese un registro en
"ventas" (en este caso, controlar que haya stock y disminuir el stock de "libros").

Cuando se activa un disparador "insert", los registros se agregan a la tabla del disparador y a una
tabla denominada "inserted". La tabla "inserted" es una tabla virtual que contiene una copia de los
registros insertados; tiene una estructura similar a la tabla en que se define el disparador, es decir,
la tabla en que se intenta la acción. La tabla "inserted" guarda los valores nuevos de los registros.

Dentro del trigger se puede acceder a esta tabla virtual "inserted" que contiene todos los registros
insertados, es lo que hicimos en el disparador creado anteriormente, lo que solicitamos es que se
le reste al "stock" de "libros", la cantidad ingresada en el nuevo registro de "ventas", valor que
recuperamos de la tabla "inserted".

"rollback transaction" es la sentencia que deshace la transacción, es decir, borra todas las
modificaciones que se produjeron en la última transacción restableciendo todo al estado anterior.

"raiserror" muestra un mensaje de error personalizado.

Para identificar fácilmente los disparadores de otros objetos se recomienda usar un prefijo y
darles el nombre de la tabla para la cual se crean junto al tipo de acción.


Práctica……………………………
Modificar la tabla ventas

create table ventas(
codigolibro int,
cantidad int
);

Aumentarle un código de venta de tipo int este campo será la llave.
Aumentarle fecha de tipo smalldate


1) Dado que los libros por algunas razones pueden ser devueltos, hacer un trigger que cada vez
que se elimine un registro en la tabla de ventas, aumente la cantidad que se vendió al stock en la
tabla de libros.

create trigger DIS_ventas_eliminar
on ventas
for delete
as
update libros set stock=stock+ (select deleted.cantidad from deleted)

from libros


2) Algunas veces, la biblioteca requiere cambiar el código del libro, y es necesario mantener la
integridad referencial, por lo cual, es necesario crear un procedimiento almacenado para que
cada vez que se modifique un código del libro en la tabla libros, se modifiquen todos los registros
en la tabla ventas al nuevo código.


Para hacer el trigger antes es necesario modificar la tabla para quitar el valor de código como
identidad y poner título como llave primaria.

Create trigger DIS_libros_modificar
on libros
for update
as
update ventas set codigolibro = (select codigo from inserted)
where codigolibro = (select codigo from deleted)



Práctica:

Hacer un disparador para que cada vez que se borre un libro de la tabla libros, se borren todos los
registros existentes de ese libro en la tabla ventas.

Hacer un disparador para que cada vez que modifique el campo cantidad en la tabla ventas haga
una de las siguientes cosas:

1) Si la cantidad nueva es menor, entonces sumar la diferencia al stock de la tabla libros. Por
ejemplo si ventas.cantidad = 5 y libros.stock = 4 y cambio ventas.cantidad = 3 entonces el
nuevo libros.stock = 6

2) Si la cantidad nueva es mayor, entonces restar la diferencia al stock de la tabla libros. Por
ejemplo si ventas.cantidad = 5 y libros.stock = 4 y cambio ventas.cantidad = 7 entonces el
nuevo libros.stock = 2

3)