Post on 27-Oct-2014
EJERCICIOS TRIGGERS 2
Javier García Cambronel PRIMERO DE ASIR
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 1
1. En una tabla se recojan los ingresos y gastos producidos por cada tienda
CONTROL_TIENDA(NIF, fecha_tran, total); cada vez que se produzca una venta o
una compra de un artículo por una tienda se debe actualizar dicha tabla.
2. Las tiendas hacen pedidos; interesa sólo servir a las tiendas que han pagado
todos sus pedidos. Los pedidos pendientes de pago están en una tabla
PEDIDOS_PENDIENTES (son pedidos que se han servido pero cuyo pago no se ha
efectuado). Los pedidos que hacen las tiendas con pedidos pendientes se
almacenarán en otra tabla PEDIDOS_NOSERVIDOS.
Cuando una tienda paga sus pedidos pendientes, estos pasan a la tabla PEDIDOS y
sus pedidos no servidos pasan a la tabla PEDIDOS_PENDIENTES.
3. Extraer los beneficios por producto y tienda.
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 2
PREPARACIÓN PARA LA PRÁCTICA
CREAMOS LA BASE DE DATOS Y LAS TABLAS
La cual todas las instrucciones se encuentran en el moodle en el siguiente enlace
http://utopico.iesribera.edu.es:8080/moodle/mod/resource/view.php?inpopup=true&id=3
483 y son las siguientes, pero necesitaban alguna corrección tanto la tabla ventas como la
tabla pedidos.
CÓDIGO DE CREACIÓN DE LAS TABLAS CORREGIDO
TABLA FABRICANTES CREATE TABLE FABRICANTES(
COD_FABRICANTE NUMERIC(3) NOT NULL,
NOMBRE NVARCHAR(15),
PAIS NVARCHAR(15),
CONSTRAINT PK_FABRICANTES PRIMARY KEY(COD_FABRICANTE)) ;
TABLA TIENDAS CREATE TABLE TIENDAS(
NIF NVARCHAR(10) NOT NULL,
NOMBRE NVARCHAR(20),
DIRECCIÓN NVARCHAR(20),
POBLACIÓN NVARCHAR(20),
PROVINCIA NVARCHAR(20),
CODPOSTAL NUMERIC(5),
CONSTRAINT PK_TIENDAS PRIMARY KEY(NIF));
TABLA ARTICULOS CREATE TABLE ARTICULOS(
ARTICULO NVARCHAR(20)NOT NULL,
COD_FABRICANTE NUMERIC(3) NOT NULL,
PESO NUMERIC(3) NOT NULL ,
CATEGORIA NVARCHAR(10) NOT NULL,
PRECIO_VENTA NUMERIC (4),
PRECIO_COSTO NUMERIC (4),
EXISTENCIAS NUMERIC (5),
CONSTRAINT PK_ARTICULOS PRIMARY KEY (ARTICULO, COD_FABRICANTE),
CONSTRAINT FK_ARTICULOS_COD_FABRICANTE FOREIGN KEY (COD_FABRICANTE)
REFERENCES FABRICANTES);
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 3
TABLA VENTAS CREATE TABLE VENTAS(
NIF NVARCHAR(10) NOT NULL,
ARTICULO NVARCHAR(20) NOT NULL,
COD_FABRICANTE NUMERIC(3) NOT NULL,
PESO NUMERIC(3) NOT NULL,
CATEGORIA NVARCHAR(10) NOT NULL,
FECHA_VENTA DATE NOT NULL,
UNIDADES_VENDIDAS NUMERIC(4),
CONSTRAINT PK_VENTAS PRIMARY KEY(NIF, ARTICULO, COD_FABRICANTE, FECHA_VENTA),
CONSTRAINT FK_VENTAS_NIF FOREIGN KEY (NIF) REFERENCES TIENDAS,
CONSTRAINT FK_VENTAS_ARTICULO FOREIGN KEY (ARTICULO, COD_FABRICANTE)
REFERENCES ARTICULOS,
CONSTRAINT FK_VENTAS_COD_FABRICANTE FOREIGN KEY (COD_FABRICANTE) REFERENCES
FABRICANTES);
TABLA PEDIDOS CREATE TABLE PEDIDOS(
NIF NVARCHAR(10) NOT NULL,
ARTICULO NVARCHAR(20) NOT NULL,
COD_FABRICANTE NUMERIC(3) NOT NULL,
PESO NUMERIC(3) NOT NULL,
CATEGORIA NVARCHAR(10) NOT NULL,
FECHA_PEDIDO DATE NOT NULL,
UNIDADES_PEDIDAS NUMERIC(4),
CONSTRAINT PK_PEDIDOS PRIMARY KEY(NIF, ARTICULO, COD_FABRICANTE,
FECHA_PEDIDO),
CONSTRAINT FK_PEDIDOS_NIF FOREIGN KEY (NIF) REFERENCES TIENDAS,
CONSTRAINT FK_PEDIDOS_ARTICULO FOREIGN KEY (ARTICULO,COD_FABRICANTE)
REFERENCES ARTICULOS,
CONSTRAINT FK_PEDIDOS_COD_FABRICANTE FOREIGN KEY (COD_FABRICANTE) REFERENCES
FABRICANTES);
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 4
EJERCICIOS TRIGGERS 2
Dada la base de datos de "tiendas, artículos, pedidos, ventas, fabricantes" que trabajamos
en el capítulo anterior. Te proponen ampliar su funcionalidad de forma que:
1. En una tabla se recojan los ingresos y gastos producidos por cada tienda
CONTROL_TIENDA(NIF, fecha_tran, total); cada vez que se produzca una venta o una compra
de un artículo por una tienda se debe actualizar dicha tabla.
CREAMOS LA TABLA CONTROL_TIENDA
Creamos la tabla donde se van a guardar los ingresos y gastos producidos por cada tienda, a
esta tabla la damos el nombre como nos indica el ejercicio de “CONTROL_TIENDA” con los
campos que se nos indican (NIF, FECHA_TRAN,TOTAL).
CREATE TABLE CONTROL_TIENDA(
NIF NVARCHAR(10),
FECHA_TRAN DATETIME,
TOTAL DECIMAL(18,0),
CONSTRAINT PK_CLAVE PRIMARY KEY (NIF,FECHA_TRAN,TOTAL),
CONSTRAINT FK_AJENA FOREIGN KEY (NIF) REFERENCES TIENDAS);
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 5
INTRODUCIMOS DATOS EN LAS TABLAS
TABLA TIENDAS: (NIF, NOMBRE, DIRECCIÓN, POBLACIÓN, PROVINCIA, CODPOSTAL)
Insert into tiendas
Values (123456,'APP','Santa Clara','Valladolid','Valladolid', 47010),
(234567,'PCBOX','Hípica','Valladolid','Valladolid',47003),
(345678,'PCCOSTE','Gondomar','Valladolid','Valladolid',47010),
(456789,'JAVIPC','Plaza mayor','Valladolid','Valladolid',47002)
TABLA FABRICANTES: (COD_FABRICANTE,NOMBRE, PAIS)
Insert into fabricantes
Values(1 ,'Varios1', 'España'),
(2 ,'Varios2' ,'España'),
(3, 'Varios3', 'España'),
(4 ,'Varios4', 'España')
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 6
TABLA ARTICULOS: (ARTICULO, COD_FABRICANTE, PESO, CATEGORIA, PRECIO_VENTA,
PRECIO_COSTO, EXISTENCIAS)
Insert into Articulos
Values ('T.sonido',1,5,'primera',50,20,20),
('T.grafica',2,10,'primera',100,60,20),
('Portatil',3,200,'segunda',600,400,10),
('Sobremesa',4,600,'segunda',700,500,10)
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 7
TRIGGER PARA CUANDO SE EFECTUAN VENTAS
Primero creamos un trigger para cuando se realizan las ventas, con el nombre que queramos
e indicando la tabla a la que hace referencia, lo segundo que le indicamos va a ser ante que
operación se va a llevar a cabo y va a entrar en funcionamiento “INSERT”, es decir después
de que se produzca un insert en la tabla ventas, se ejecutara el trigger en el cual indicamos a
partir del begin que se inserte “INSERT INTO” en la tabla “control_tienda” el NIF, la fecha de
transacción y el total, para ello hacemos un “SELECT” de NIF, después de “Getdate()” que
insertara la fecha actual del sistema y por último el “TOTAL” que viene dado por el precio de
venta de cada unidad multiplicado por las unidades vendidas, dándonos así el valor total que
han gastado en dicho pedido, Por último tendríamos que hacer la unión de las tablas
correspondientes entre “ARTICULOS Y VENTAS” y la comparación vara que los valores sean
coherentes de ARTICULO
CREATE TRIGGER dbo.mov ON VENTAS
FOR INSERT
AS
BEGIN
INSERT INTO CONTROL_TIENDA(NIF, FECHA_TRAN,TOTAL)
SELECT NIF,GETDATE(),(PRECIO_VENTA*UNIDADES_VENDIDAS)
FROM ARTICULOS INNER JOIN VENTAS ON ARTICULOS.ARTICULO=VENTAS.ARTICULO
END
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 8
COMPROBACION DEL TRIGGER DE VENTAS
Ahora lo que hacemos va a ser comprobar el funcionamiento, para ello insertamos en la
tabla ventas un pedido, de los artículos que se encuentran en nuestra base de datos.
INSERT INTO VENTAS VALUES (123456, 'T.sonido',1,5,'Primera','29-5-2012',2)
Comprobamos, consultando la tabla ventas, si se ha producido la inserción del pedido
correctamente y vemos que así es.
Por último comprobamos todos los elementos que se encuentran en la tabla control_tienda
y vemos que se nos muestran perfectamente todos los datos, el NIF de la tienda, la FECHA de
la transacción y el TOTAL del importe.
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 9
TRIGGER PARA CUANDO SE EFECTUAN PEDIDOS
Como vemos este trigger es muy parecido al anterior, le llamaremos en este caso mov1 y
hará referencia a la tabla “PEDIDOS” cuando en esta, se produzca la accion que le indicamos,
en este caso como vemos, ante un “INSERT” Ahora vemos que las acciones del trigger son las
mismas que en el anterior, es decir introducir los datos a la tabla “CONTROL_TIENDA” dichos
datos del “SELECT que indicamos los datos a recoger, con la única diferencia con el anterior
trigger en el que el “TOTAL”, se verá reflejado por el precio de costo multiplicado por las
unidades pedidas.
CREATE TRIGGER dbo.mov1 ON PEDIDOS
FOR INSERT
AS
BEGIN
INSERT INTO CONTROL_TIENDA(NIF, FECHA_TRAN,TOTAL)
SELECT NIF,GETDATE(),(PRECIO_COSTO*UNIDADES_PEDIDAS)
FROM ARTICULOS INNER JOIN PEDIDOS ON ARTICULOS.ARTICULO=PEDIDOS.ARTICULO
END
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 10
COMPROBACION DEL TRIGGER DE PEDIDOS
Comprobamos el trigger y hacemos un pedido cualquiera, como el que vemos a
continuación.
INSERT INTO PEDIDOS VALUES (123456, 'T.sonido',1,5,'Primera','29-5-2012',2)
Como podemos ver consultando la tabla pedidos, el pedido se ha llevado a cabo
perfectamente.
Por último comprobamos si esto también ha tendido efecto en la tabla “control_tienda” y
como vemos así es.
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 11
2. Las tiendas hacen pedidos; interesa sólo servir a las tiendas que han pagado todos sus
pedidos. Los pedidos pendientes de pago están en una tabla PEDIDOS_PENDIENTES (son
pedidos que se han servido pero cuyo pago no se ha efectuado). Los pedidos que hacen las
tiendas con pedidos pendientes se almacenarán en otra tabla PEDIDOS_NOSERVIDOS.
Cuando una tienda paga sus pedidos pendientes, estos pasan a la tabla PEDIDOS y sus
pedidos no servidos pasan a la tabla PEDIDOS_PENDIENTES.
CREAMOS LA TABLA PEDIDOS_PENDIENTES
CREATE TABLE PEDIDOS_PENDIENTES(
NIF NVARCHAR(10) NOT NULL,
ARTICULO NVARCHAR(20) NOT NULL,
COD_FABRICANTE NUMERIC(3) NOT NULL,
PESO NUMERIC(3) NOT NULL,
CATEGORIA NVARCHAR(10) NOT NULL,
FECHA_PEDIDO DATE NOT NULL,
UNIDADES_PEDIDAS NUMERIC(4),
PAGADO BIT DEFAULT 'FALSE' NOT NULL,
CONSTRAINT PK_PEDIDOS_PENDIENTES PRIMARY KEY(NIF, ARTICULO, COD_FABRICANTE,
FECHA_PEDIDO),
CONSTRAINT FK_PEDIDOS_PENDIENTES_NIF FOREIGN KEY (NIF) REFERENCES TIENDAS,
CONSTRAINT FK_PEDIDOS_PENDIENTES_ARTICULO FOREIGN KEY
(ARTICULO,COD_FABRICANTE) REFERENCES ARTICULOS,
CONSTRAINT FK_PEDIDOS_PENDIENTES_COD_FABRICANTE FOREIGN KEY
(COD_FABRICANTE) REFERENCES FABRICANTES)
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 12
CREAMOS LA TABLA PEDIDOS_NOSERVIDOS
CREATE TABLE PEDIDOS_NOSERVIDOS (
NIF NVARCHAR(10) NOT NULL,
ARTICULO NVARCHAR(20) NOT NULL,
COD_FABRICANTE NUMERIC(3) NOT NULL,
PESO NUMERIC(3) NOT NULL,
CATEGORIA NVARCHAR(10) NOT NULL,
FECHA_PEDIDO DATE NOT NULL,
UNIDADES_PEDIDAS NUMERIC(4),
CONSTRAINT PK_PEDIDOS_NOSERVIDOS PRIMARY KEY(NIF, ARTICULO, COD_FABRICANTE,
FECHA_PEDIDO),
CONSTRAINT FK_PEDIDOS_NOSERVIDOS_NIF FOREIGN KEY (NIF) REFERENCES TIENDAS,
CONSTRAINT FK_PEDIDOS_NOSERVIDOS_ARTICULO FOREIGN KEY
(ARTICULO,COD_FABRICANTE) REFERENCES ARTICULOS,
CONSTRAINT FK_PEDIDOS_NOSERVIDOS_COD_FABRICANTE FOREIGN KEY
(COD_FABRICANTE) REFERENCES FABRICANTES)
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 13
TRIGGER QUE DETERMINA SI EL PEDIDO ACABA EN
PEDIDOS_PENDIENTES O PASA A PEDIDOS_NOSERVIDOS
Creamos el trigger que va a actuar sobre la tabla “PEDIDOS_PENDIENTES” con la acción
“INSTEAD OF INSERT” que se diferencia del “FOR INSERT” que se utiliza habitualmente, en
que esta relacionado con una vista, en este caso la vista que tenemos en cuenta va a ser que
si el NIF de la tienda que insertamos coincide con un NIF que tengamos ya en
“PEDIDOS_PENDIENTES” la inserción se haga en “PEDIDOS_NOSERVIDOS” y si no “ELSE” que
se introduzca en “PEDIDOS_PENDIENTES” es decir, que el producto se sirva, aunque no este
pagado.
CREATE TRIGGER dbo.mov3 ON PEDIDOS_PENDIENTES
INSTEAD OF INSERT
AS
BEGIN
IF (SELECT NIF FROM INSERTED) IN (SELECT NIF FROM PEDIDOS_PENDIENTES)
BEGIN
INSERT INTO
PEDIDOS_NOSERVIDOS(NIF,ARTICULO,COD_FABRICANTE,PESO,CATEGORIA,FECHA_PEDIDO,
UNIDADES_PEDIDAS)
SELECT
i.NIF,i.ARTICULO,i.COD_FABRICANTE,i.PESO,i.CATEGORIA,i.FECHA_PEDIDO,i.UNIDADES_PEDI
DAS
FROM INSERTED i
END
ELSE
BEGIN
INSERT INTO.
PEDIDOS_PENDIENTES(NIF,ARTICULO,COD_FABRICANTE,PESO,CATEGORIA,FECHA_PEDIDO,U
NIDADES_PEDIDAS)
SELECT
i.NIF,i.ARTICULO,i.COD_FABRICANTE,i.PESO,i.CATEGORIA,i.FECHA_PEDIDO,i.UNIDADES_PEDI
DAS
FROM INSERTED i
END
END
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 14
TRIGGER QUE ACTUALIZA LA SITUACIÓN LOS PEDIDOS DESPUES DE UN
UPDATE SOBRE “PAGADO” EN PEDIDOS_PENDIENTES
Creamos un trigger, para que después de un UPDATE, se compruebe si el campo “PAGADO”
de pedidos pendientes esta en “TRUE” y si es así insertar ese pedido que estaba en
“PEDIDOS_PENDIENTES” en la tabla “PEDIDOS” y después con “DELETE” utilizamos el NIF
para borrar dicho pedido de la tabla “PEDIDOS_PENDIENTES”, Una vez hecho esto
insertamos en la tabla “PEDIDOS_PENDIENTES” el último pedido referente a esa tienda
utilizando el “NIF” que se encontrara en la tabla “PEDIDOS_NOSERVIDOS”.
CREATE TRIGGER dbo.mov4 ON PEDIDOS_PENDIENTES
AFTER UPDATE AS
BEGIN
IF (SELECT PAGADO FROM PEDIDOS_PENDIENTES) = 'TRUE'
BEGIN
INSERT INTO
PEDIDOS(NIF,ARTICULO,COD_FABRICANTE,PESO,CATEGORIA,FECHA_PEDIDO,UNIDADES_PE
DIDAS)
SELECT
NIF,ARTICULO,COD_FABRICANTE,PESO,CATEGORIA,FECHA_PEDIDO,UNIDADES_PEDIDAS
FROM PEDIDOS_PENDIENTES
DELETE FROM PEDIDOS_PENDIENTES WHERE NIF = (SELECT i.NIF FROM INSERTED i)
INSERT INTO
PEDIDOS_PENDIENTES(NIF,ARTICULO,COD_FABRICANTE,PESO,CATEGORIA,FECHA_PEDIDO,U
NIDADES_PEDIDAS)
SELECT
NIF,ARTICULO,COD_FABRICANTE,PESO,CATEGORIA,FECHA_PEDIDO,UNIDADES_PEDIDAS
FROM PEDIDOS_NOSERVIDOS
WHERE NIF NOT IN (SELECT NIF FROM PEDIDOS_PENDIENTES)
DELETE FROM PEDIDOS_NOSERVIDOS WHERE NIF = (SELECT i.NIF FROM INSERTED i)
END
END
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 15
COMPROBAMOS EL FUNCIONAMIENTO DE LOS TRIGGER
“EJEMPLO SOBRE UNA TIENDA”
Insertamos un pedido cualquiera en la tabla “PEDIDOS_PENDIENTES” para ello ejecutamos la
siguiente instruccion.
INSERT INTO PEDIDOS_PENDIENTES
VALUES (456789,'Sobremesa',4,600,'segunda','29-5-2012',5,'FALSE')
Vemos que haciendo la consulta pertinente el pedido se encuentra en
“PEDIDOS_PENDIENTES”
Y que en la tabla “PEDIDOS” no está.
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 16
Volvemos a insertar un pedido de la misma tienda en “PEDIDOS_PENDIENTES” ejecutando la
siguiente instrucción
INSERT INTO PEDIDOS_PENDIENTES
VALUES (456789,'Portatil',3,200,'segunda','30-5-2012',2, 'FALSE')
Como podemos ver este no se guarda en la tabla “PEDIDOS_PENDIENTES”
Sino que se guarda como podemos ver debido a la ejecución del trigger en la tabla
“PEDIDIOS_NOSERVIDOS”
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 17
Al hacer un “UPDATE” sobre la tabla pedidos pendientes, actualizando el campo “PAGADO”
con “SET” con el valor de “TRUE” donde el “NIF” es el de la tienda donde se han hecho los
pedidos anteriores, vemos que la instrucción se ejecuta perfectamente.
UPDATE PEDIDOS_PENDIENTES
SET PAGADO = 'TRUE'
WHERE NIF = 456789
Y vemos que ahora en la tabla “PEDIDOS_NOSERVIDOS” no se encuentra ningún pedido.
El pedido que se encuentra ahora en esta tabla es el que se encontraba antes de hacer el
“UPDATE” en la tabla “PEDIDOS_NOSERVIDOS” de esta forma solo se podrá encontrar un
pedido enviado pero no pagado de cada tienda, que son los que están en esta tabla.
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 18
Y el pedido que se encontraba en la tabla “PEDIDOS_PENDIENTES” al hacer el “UPDATE”
pertinente en el que hemos cambiado el valor del campo “PAGADO” a “TRUE” el pedido
como podemos comprobar ha pasado a la tabla “PEDIDOS”
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 19
3. Extraer los beneficios por producto y tienda.
Hacemos una selección del NIF, del nombre de la tienda, y articulo de la tabla pedidos del
cual vamos a hacer la suma unidades vendidas por precio de venta todo ello restado por
unidades pedidas y el precio de costo (nos sonara pues son operaciones que habíamos hecho
en los triggers del primer ejercicio sobre la tabla “Control_tienda”) para así obtener los
beneficios nombrando con ese nombre como se nos pide en el ejercicio con “AS” unimos las
tablas pertinentes y hacemos la comparación para que el NIF de tiendas sea igual al de
pedidos, al igual que para ventas y también para que el articulo sea el mismo cuando se
consulte a la tabla pedidos y artículos y así tengan coherencia los resultados y hacemos el
“group by” pertinente, es decir, de los parámetros que hemos utilizado en el “SELECT”
SELECT
TIENDAS.NIF,NOMBRE,PEDIDOS.ARTICULO,(SUM(UNIDADES_VENDIDAS)*PRECIO_VENTA)-
(SUM(UNIDADES_PEDIDAS)*PRECIO_COSTO) AS BENEFICIOS
FROM ((TIENDAS INNER JOIN PEDIDOS ON TIENDAS.NIF = PEDIDOS.NIF)
INNER JOIN VENTAS ON TIENDAS.NIF = VENTAS.NIF)
INNER JOIN ARTICULOS ON PEDIDOS.ARTICULO = ARTICULOS.ARTICULO
GROUP BY TIENDAS.NIF,NOMBRE,PEDIDOS.ARTICULO,PRECIO_VENTA,PRECIO_COSTO
Vemos que el precio de venta del artículo es 50 y el precio de costo es 20.
Vemos las ventas que se han realizado que son dos es decir un total de 2X50E=100 euros en
ventas.
Y vemos que el total que hemos pedido también son 2 con lo que lo que hemos gastado en
pedidos es 2X20E=40 euros en pedidos.
[EJERCICIOS TRIGGERS 2]
PRIMERO DE ASIR Página 20
Con lo que las Ganancias serian lo referente a ventas menos lo que hemos gastado en
pedidos, es decir 100E-40E= 60E que como vemos es el resultado que obtenemos.