Ejercicios de Triggers (II)

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,


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,


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 - 4 - de 13

/*TABLA Articulos*/

INSERT INTO Articulos

VALUES ('Galaxy SIII', 21235, '100', 'Smartphone', 6, 6, 30)


VALUES ('Desire HD', 21235, '100', 'Smartphone', 7, 7, 50)


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)


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:


VALUES ('Galaxy Nexus', 21235, '100', 'Smartphone', 6, 6, 30)

Resultados:

(1 row(s) affected).


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:


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


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:



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



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:



Y ahora comprobamos que se actualiza.

Comandos:

INSERT INTO



VALUES (98642315, 'Galaxy SIII', 21235, 100, 'Smartphone',

'01/01/2012', 5)

Resultados:





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

Ejercicios de Triggers (II)

Documents

Transcript of Ejercicios de Triggers (II)