SQL - Ejercicios triggers

Queremos controlar algunas restricciones mediante  triggers: (debes crear dos disparadores, uno para cada ejercicio):

1.    Cada vez que se vaya a insertar o modificar una línea de un pedido debe de actualizarse correctamente el importe de la misma (cantidad X precio del producto).

2.    Cada vez que se inserten, se borren o modifiquen líneas hay que actualizar el coste total del pedido correspondiente

CREATE TABLE CLIENTES (

CODIGO NUMBER(4,0)  PRIMARY KEY,

NOMBRE VARCHAR2(30) NOT NULL,

APELLIDOS VARCHAR2(30) NOT NULL,

EDAD	NUMBER(2,0) NOT NULL

);

CREATE TABLE PEDIDOS (

NUM	NUMBER(5,0) PRIMARY KEY,

FECHA	DATE NOT NULL,

GASTOS_ENVIO	NUMBER(5,2),

FECHA_PREVISTA DATE NOT NULL,

TOTAL NUMBER(10,2) ,

CLIENTE NUMBER(4,0),

CONSTRAINT CLIENTES_FK FOREIGN KEY (CLIENTE) REFERENCES CLIENTES (CODIGO)

);

CREATE TABLE PRODUCTOS (

CODIGO  NUMBER(5,0) PRIMARY KEY,

NOMBRE  VARCHAR2(30) NOT NULL,

PRECIO   NUMBER(7,2) NOT NULL

);

CREATE TABLE  LINEAS    (

NUM NUMBER(2,0),

NUM_PEDIDO NUMBER(5,0),

PRODUCTO NUMBER(5,0) NOT NULL ,

CANTIDAD NUMBER(8,0) NOT NULL ,

IMPORTE NUMBER(6,2),

 CONSTRAINT DETALLE_PK PRIMARY KEY (NUM, NUM_PEDIDO) ,

 CONSTRAINT PEDIDO_FK FOREIGN KEY (NUM_PEDIDO)

  REFERENCES  PEDIDOS (NUM) ,

 CONSTRAINT PRODUCTO_FK FOREIGN KEY (PRODUCTO)

  REFERENCES  PRODUCTOS (CODIGO)

   );

INSERT INTO CLIENTES (CODIGO, NOMBRE, APELLIDOS, EDAD)

SELECT 0001, 'Luis', 'Garcia Perez', 30 FROM DUAL UNION ALL

SELECT 0002, 'Maria', 'Lopez Garrido', 50 FROM DUAL UNION ALL

SELECT 0003, 'Javier', 'Gamez Valiente', 30 FROM DUAL UNION ALL

SELECT 0004, 'Luis Mª', 'Rico Martin', 17 FROM DUAL UNION ALL

SELECT 0005, 'Ana Belen', 'Dimas Marco', 15 FROM DUAL UNION ALL

SELECT 0006, 'Jose Luis', 'Garcia Sanchez', 50 FROM DUAL UNION ALL

SELECT 0007, 'Mª Pilar', 'Perez Bermejo', 45 FROM DUAL;

INSERT INTO PEDIDOS (NUM, FECHA, GASTOS_ENVIO, FECHA_PREVISTA, TOTAL, CLIENTE)

SELECT 1, TO_DATE('10/10/2015', 'DD/MM/YYYY'), NULL, TO_DATE('10/11/2015', 'DD/MM/YYYY'), 310, '0001' FROM DUAL UNION ALL

SELECT 2, TO_DATE('10/02/2016', 'DD/MM/YYYY'), 5, TO_DATE('10/03/2016', 'DD/MM/YYYY'), 185, '0001' FROM DUAL UNION ALL

SELECT 3, TO_DATE('20/02/2016', 'DD/MM/YYYY'), 3, TO_DATE('20/04/2016', 'DD/MM/YYYY'), 180, '0001' FROM DUAL UNION ALL

SELECT 4, TO_DATE('25/03/2016', 'DD/MM/YYYY'), NULL, TO_DATE('20/04/2016', 'DD/MM/YYYY'), 100, '0002' FROM DUAL UNION ALL

SELECT 5, TO_DATE('25/03/2016', 'DD/MM/YYYY'), NULL, TO_DATE('20/05/2016', 'DD/MM/YYYY'), 135, '0003' FROM DUAL UNION ALL

SELECT 6, TO_DATE('15/04/2016', 'DD/MM/YYYY'), NULL, TO_DATE('20/05/2016', 'DD/MM/YYYY'), 45, '0004' FROM DUAL UNION ALL

SELECT 7, TO_DATE('15/04/2016', 'DD/MM/YYYY'), NULL, TO_DATE('20/05/2016', 'DD/MM/YYYY'), 45, '0005' FROM DUAL UNION ALL

SELECT 8, TO_DATE('15/05/2016', 'DD/MM/YYYY'), 10, TO_DATE('20/06/2016', 'DD/MM/YYYY'), 45, '0006' FROM DUAL UNION ALL

SELECT 9, TO_DATE('15/07/2016', 'DD/MM/YYYY'), 10, TO_DATE('20/09/2016', 'DD/MM/YYYY'), 85, '0007' FROM DUAL UNION ALL

SELECT 10, TO_DATE('15/01/2017', 'DD/MM/YYYY'), 10, TO_DATE('15/02/2017', 'DD/MM/YYYY'), 90, '0007' FROM DUAL;

INSERT INTO PRODUCTOS (CODIGO, NOMBRE, PRECIO)

SELECT 10001, 'PANTALÓN', 50 FROM DUAL UNION ALL

SELECT 10002, 'PANTALÓN PITILLO', 60 FROM DUAL UNION ALL

SELECT 10003, 'PANTALÓN CAMPANA', 55 FROM DUAL UNION ALL

SELECT 20001, 'CAMISA M/L', 65 FROM DUAL UNION ALL

SELECT 20002, 'CAMISA M/C', 45 FROM DUAL UNION ALL

SELECT 30001, 'VESTIDO C', 80 FROM DUAL UNION ALL

SELECT 30002, 'VESTIDO L', 90 FROM DUAL UNION ALL

SELECT 40001, 'FALDA LARGA', 50 FROM DUAL UNION ALL

SELECT 40002, 'FALDA CORTA', 45 FROM DUAL UNION ALL

SELECT 40003, 'FALDA MINI', 40 FROM DUAL;

INSERT INTO LINEAS (NUM, NUM_PEDIDO, PRODUCTO, CANTIDAD, IMPORTE)

SELECT 1, 1, 10001, 2, 100 FROM DUAL UNION ALL

SELECT 2, 1, 30001, 1, 80 FROM DUAL UNION ALL

SELECT 3, 1, 20001, 2, 130 FROM DUAL UNION ALL

SELECT 1, 2, 20001, 1, 65 FROM DUAL UNION ALL

SELECT 2, 2, 40003, 3, 120 FROM DUAL UNION ALL

SELECT 1, 3, 40002, 2, 180 FROM DUAL UNION ALL

SELECT 1, 4, 10001, 2, 100 FROM DUAL UNION ALL

SELECT 1, 5, 20002, 2, 90 FROM DUAL UNION ALL

SELECT 2, 5, 40002, 1, 45 FROM DUAL UNION ALL

SELECT 1, 6, 40002, 1, 45 FROM DUAL UNION ALL

SELECT 1, 7, 40002, 1, 45 FROM DUAL UNION ALL

SELECT 1, 8, 40002, 1, 45 FROM DUAL UNION ALL

SELECT 1, 9, 40003, 1, 40 FROM DUAL UNION ALL

SELECT 2, 9, 20002, 1, 45 FROM DUAL UNION ALL

SELECT 1, 10, 20002, 2, 90 FROM DUAL;