Ejercicios de triggers (II).
Alejandro Alonso Taratiel. 1º ASIR. Base de datos. Prof.: Santiago Blanco.
ÍNDICE Ejercicios Ejercicios de triggers. triggers. ................... ..................................... .................................. .................................. ................................... .................................- 2 Tablas. Tablas. ................................... .................................................... .................................. .................................. .................................. .......................................- 2 Ejercicio_1 Ejercicio_1..................................... ...................................................... .................................. .................................. ................................... ......................- 6 Ejercicio_2 Ejercicio_2..................................... ...................................................... .................................. .................................. ................................... ......................- 8 Ejercicio_3 Ejercicio_3..................................... ...................................................... .................................. .................................. ................................... ..................- 12 -
Página - 1 - de 13
Bases de Datos.
Alejandro Alonso Taratiel
31/05/2012
Ejercicios de triggers. Tablas. /*CREACCIÓN DE LA BASE BASE DE DATOS*/ CREATE DATABASE Tiendas ON (NAME =Tiendas,FILENAME = 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Tiendas.mdf') LOG ON(NAME=Tiendas_log, FILENAME='C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\Tiendas.ldf') /*TABLA Frabricantes*/ CREATE TABLE Fabricantes (Cod_fabricante INT NOT NULL PRIMARY PRIMARY KEY, Nombre VARCHAR(15), Pais VARCHAR(15)); /*TABLA DE Articulos*/ CREATE TABLE Articulos (Articulo VARCHAR(20)NOT NULL, Cod_fabricante INT I NT NOT NULL CONSTRAINT CONSTRAINT fk1 FOREIGN KEY REFERENCES Fabricantes (Cod_fabricante), Peso INT NOT NULL, Categoria VARCHAR(10) NOT NULL, Precio_venta INT, Precio_costo INT, Existencias INT, PRIMARY KEY (Articulo, Cod_fabricante, Peso, Categoria)); /*TABLA Tiendas*/ CREATE TABLE Tiendas (NIF VARCHAR(10) NOT NULL PRIMARY KEY, Nombre VARCHAR(20), Direccion VARCHAR(20), Poblacion VARCHAR(20), Provincia VARCHAR(20), CodPostal INT);
Página - 2 - de 13
/*TABLA Pedidos*/ CREATE TABLE Pedidos (NIF VARCHAR(10) NOT NULL, Articulo VARCHAR(20) NOT NULL, Cod_fabricante INT I NT NOT NULL, Peso INT NOT NULL, Categoria VARCHAR(10) NOT NULL, Fecha_pedido DATE NOT NULL, Unidades_pedidas INT, PRIMARY KEY (NIF, Articulo, Cod_fabricante, Fecha_Pedido), FOREIGN KEY (NIF) REFERENCES Tiendas (NIF), FOREIGN KEY (Articulo, Cod_fabricante, Peso, Categoria) REFERENCES Articulos (Articulo, Cod_fabricante, Peso, Categoria)); /*TABLA VENTAS*/ VENTAS*/ CREATE TABLE Ventas (NIF VARCHAR(10) NOT NULL, Articulo VARCHAR(20) NOT NULL, Cod_fabricante INT I NT NOT NULL, Peso INT NOT NULL, Categoria VARCHAR(10) NOT NULL, Fecha_venta DATE NOT NULL, Unidades_vendidas INT, KEY (NIF, Articulo, Cod_fabricante, Fecha_Venta), FOREIGN KEY (NIF) REFERENCES Tiendas (NIF), FOREIGN KEY (Articulo, Cod_fabricante, Peso, Categoria) REFERENCES Articulos (Articulo, Cod_fabricante, Peso, Categoria)); /*INSERTAR DATOS*/ /*TABLA Fabricantes*/ INSERT INTO Fabricantes VALUES (21235, 'Samsung', 'Corea' ) INSERT INTO Fabricantes VALUES (56781, 'HTC', 'Taiwan')
Página - 3 - de 13
Bases de Datos.
Alejandro Alonso Taratiel
31/05/2012
/*TABLA Articulos*/ INSERT INTO Articulos VALUES ('Galaxy SIII', 21235, '100', 'Smartphone', 6, 6, 30) INSERT INTO Articulos VALUES ('Desire HD', 21235, '100', 'Smartphone', 7, 7, 50) INSERT INTO Articulos VALUES ('One X', 56781, '500', 'Smartphone', 20, 20, 40) /*TABLA Tiendas*/ INSERT INTO Tiendas VALUES (98642315, 'Phone house', 'Calle Santiago', 'Valladolid', 'Valladolid', '47003') INSERT INTO Tiendas VALUES (45625891,'Vodafone', 'Calle Duque de la victoria', 'Valladolid', 'Valladolid', '47003') INSERT INTO Tiendas VALUES (65835470,'Moviestar', 'Calle Miguel Iscar', 'Valladolid', 'Valladolid', '47003') /*TABLA Pedidos*/ INSERT INTO Pedidos VALUES (98642315, 'Galaxy SIII', 21235, 100, 'Smartphone', '01/01/2012', 5) INSERT INTO Pedidos VALUES (65835470, 'Galaxy SIII', 21235, 100, 'Smartphone', '02/01/2012', 200) INSERT INTO Pedidos VALUES (98642315, 'Desire HD', 21235, 100, 'Smartphone', '01/01/2012', 1000) INSERT INTO Pedidos VALUES (45625891, 'Desire HD', 21235, 100, 'Smartphone', '02/01/2012', 2) INSERT INTO Pedidos VALUES (45625891, 'One X', 56781 , 500, 'Smartphone', '01/01/2012', 150) INSERT INTO Pedidos VALUES (65835470, 'One X', 56781 , 500, 'Smartphone', '02/01/2012', 10)
Página - 4 - de 13
/*TABLA Ventas*/ INSERT INTO Ventas VALUES (98642315, 'Galaxy SIII', 21235, 100, 'Smartphone', '03/01/2012', 2) INSERT INTO Ventas VALUES (65835470, 'Desire HD', 21235 , 100, 'Smartphone', '03/01/2012', 3) INSERT INTO Ventas VALUES (98642315, 'One X', 56781, 500, 'Smartphone', '03/01/2012', 10)
Página - 5 - de 13
Bases de Datos.
Alejandro Alonso Taratiel
31/05/2012
Ejercicio_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. Comandos:
CREATE TRIGGER dbo.movimientos ON Articulos FOR INSERT AS BEGIN INSERT INTO I NTO CONTROL_TIENDA(NIF, FECHA_TRAN,TOTAL) SELECT NIF,GETDATE(),(PRECIO_VENTA*UNIDADES_VENDIDAS) FROM ARTICULOS INNER JOIN VENTAS ON ARTICULOS.ARTICULO=VENTAS.ARTICULO END Resultados:
Command(s) completed successfully.
Página - 6 - de 13
Y ahora comprobamos que se actualiza. Comandos:
INSERT INTO Articulos VALUES ('Galaxy Nexus', 21235, '100', 'Smartphone', 6, 6, 30) Resultados:
(1 row(s) affected).
Página - 7 - de 13
Bases de Datos.
Alejandro Alonso Taratiel
31/05/2012
Ejercicio_2 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. Comandos:
CREATE TABLE PEDIDOS_PENDIENTES( NIF VARCHAR(10) NOT NULl, ARTICULO VARCHAR(40) NOT NULL, COD_FABRICANTE NUMERIC(8) NOT NULL, PESO NUMERIC(4) NOT NULL, CATEGORIA VARCHAR(30) NOT NULL, FECHA_PEDIDO DATE NOT NULL, UNIDADES_PEDIDAS NUMERIC(3), PAGADO BIT DEFAULT 'FALSE' NOT NULL); CREATE TABLE PEDIDOS_NOSERVIDOS( NIF VARCHAR(10) NOT NULL, ARTICULO VARCHAR(20) NOT NULL, COD_FABRICANTE NUMERIC(3) NOT NULL, PESO NUMERIC(3) NOT NULL, CATEGORIA VARCHAR(10) NOT NULL, FECHA_PEDIDO DATE NOT NULL, UNIDADES_PEDIDAS NUMERIC(4)) Resultados:
Command(s) completed successfully.
Página - 8 - de 13
Ahora creamos los triggers. Comandos:
CREATE TRIGGER dbo.pagos ON PEDIDOS_PENDIENTES 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,PES O,CATEGORIA,FECHA_PEDIDO,UNIDADES_PEDIDAS) SELECT i.NIF,i.ARTICULO,i.COD_FABRICANTE,i.PESO,i i.NIF,i.ARTICULO,i.COD_FABRICANTE,i.PESO,i.CATEGORIA,i.FE .CATEGORIA,i.FE CHA_PEDIDO,i.UNIDADES_PEDIDAS FROM INSERTED i END ELSE BEGIN INSERT INTO. PEDIDOS_PENDIENTES(NIF,ARTICULO,COD_FABRICANTE,PES O,CATEGORIA,FECHA_PEDIDO,UNIDADES_PEDIDAS) SELECT i.NIF,i.ARTICULO,i.COD_FABRICANTE,i.PESO,i i.NIF,i.ARTICULO,i.COD_FABRICANTE,i.PESO,i.CATEGORIA,i.FE .CATEGORIA,i.FE CHA_PEDIDO,i.UNIDADES_PEDIDAS FROM INSERTED i END END Resultados:
Command(s) completed successfully.
Página - 9 - de 13
Bases de Datos.
Alejandro Alonso Taratiel
31/05/2012
Comandos:
CREATE TRIGGER dbo.Pagado ON PEDIDOS_PENDIENTES 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_PEDIDAS) SELECT NIF,ARTICULO,COD_FABRICANTE,PESO,CA NIF,ARTICULO,COD_FABRICANTE,PESO,CATEGORIA,FECHA TEGORIA,FECHA_P _P EDIDO,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,PES O,CATEGORIA,FECHA_PEDIDO,UNIDADES_PEDIDAS) SELECT NIF,ARTICULO,COD_FABRICANTE,PESO,CA NIF,ARTICULO,COD_FABRICANTE,PESO,CATEGORIA,FECHA TEGORIA,FECHA_P _P EDIDO,UNIDADES_PEDIDAS FROM PEDIDOS_NOSERVIDOS WHERE NIF NOT IN (SELECT NIF FROM PEDIDOS_PENDIENTES) DELETE FROM PEDIDOS_NOSERVIDOS WHERE NIF = (SELECT (SE LECT i.NIF FROM INSERTED i) END END; Resultados:
Command(s) completed successfully.
Página - 10 - de 13
Y ahora comprobamos que se actualiza. Comandos:
INSERT INTO PEDIDOS_PENDIENTES(NIF,ARTICULO,COD_FABRICANTE,PES O,CATEGORIA,FECHA_PEDIDO,UNIDADES_PEDIDAS) VALUES (98642315, 'Galaxy SIII', 21235, 100, 'Smartphone', '01/01/2012', 5) Resultados:
(1 row(s) affected). (1 row(s) affected).
Página - 11 - de 13
Bases de Datos.
Alejandro Alonso Taratiel
31/05/2012
Ejercicio_3 3. Extraer los beneficios por producto y tienda. Comandos:
SELECT T.NIF,NOMBRE,P.ARTICULO,(SUM(UNIDADES_VENDIDAS)*PR ECIO_VENTA)-(SUM(UNIDADES_PEDIDAS)*PRECIO_COSTO) AS BENEFICIOS FROM ((TIENDAS T INNER JOIN PEDIDOS P ON T.NIF T .NIF = P.NIF) INNER JOIN VENTAS V ON T.NIF = V.NIF) INNER JOIN ARTICULOS A ON P.ARTICULO = A.ARTICULO GROUP BY T.NIF,NOMBRE,P.ARTICULO,PRECIO_VENTA,PRECIO_COSTO Resultados:
65835470 65835470 98642315 98642315
Moviestar Moviestar Phone house Phone house
Galaxy SIII One X Desire HD Galaxy SIII
Página - 12 - de 13
-1182 -140 -13916 - 13916 12