View
127
Download
1
Category
Tags:
Preview:
Citation preview
Ejercicios de triggers
(II).
Alejandro Alonso Taratiel. 1º ASIR.
Base de datos. Prof.: Santiago Blanco.
.
Página - 1 - de 13
ÍNDICE
Ejercicios de triggers. ................................................................................................- 2 - Tablas. ..................................................................................................................- 2 -
Ejercicio_1 ............................................................................................................- 6 - Ejercicio_2 ............................................................................................................- 8 -
Ejercicio_3 .......................................................................................................... - 12 -
Bases de Datos. Alejandro Alonso Taratiel 31/05/2012
Página - 2 - de 13
Ejercicios de triggers.
Tablas.
/*CREACCIÓN DE LA 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 KEY,
Nombre VARCHAR(15),
Pais VARCHAR(15));
/*TABLA DE Articulos*/
CREATE TABLE Articulos
(Articulo VARCHAR(20)NOT NULL,
Cod_fabricante INT NOT NULL 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 - 3 - de 13
/*TABLA Pedidos*/
CREATE TABLE Pedidos
(NIF VARCHAR(10) NOT NULL,
Articulo VARCHAR(20) NOT NULL,
Cod_fabricante INT 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*/
CREATE TABLE Ventas
(NIF VARCHAR(10) NOT NULL,
Articulo VARCHAR(20) NOT NULL,
Cod_fabricante INT 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')
Bases de Datos. Alejandro Alonso Taratiel 31/05/2012
Página - 4 - de 13
/*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 - 5 - 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)
Bases de Datos. Alejandro Alonso Taratiel 31/05/2012
Página - 6 - de 13
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 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 - 7 - 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).
Bases de Datos. Alejandro Alonso Taratiel 31/05/2012
Página - 8 - de 13
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 - 9 - de 13
Ahora creamos los triggers.
Comandos:
CREATE TRIGGER dbo.pagos 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,PES
O,CATEGORIA,FECHA_PEDIDO,UNIDADES_PEDIDAS)
SELECT
i.NIF,i.ARTICULO,i.COD_FABRICANTE,i.PESO,i.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.CATEGORIA,i.FE
CHA_PEDIDO,i.UNIDADES_PEDIDAS
FROM INSERTED i
END
END
Resultados:
Command(s) completed successfully.
Bases de Datos. Alejandro Alonso Taratiel 31/05/2012
Página - 10 - de 13
Comandos:
CREATE TRIGGER dbo.Pagado 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_PEDIDAS)
SELECT
NIF,ARTICULO,COD_FABRICANTE,PESO,CATEGORIA,FECHA_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,CATEGORIA,FECHA_P
EDIDO,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;
Resultados:
Command(s) completed successfully.
Página - 11 - 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).
Bases de Datos. Alejandro Alonso Taratiel 31/05/2012
Página - 12 - de 13
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 = 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 Moviestar Galaxy SIII -1182
65835470 Moviestar One X -140 98642315 Phone house Desire HD -13916 98642315 Phone house Galaxy SIII 12
Recommended