--1)
CREATE TABLE TOUR AS SELECT * FROM DOCENCIA.TOUR2017;
--SELECT * FROM TOUR;

--2)
CREATE TABLE INFO_PERSONAL AS SELECT ID,NAME,NATIONALITY FROM TOUR;
ALTER TABLE INFO_PERSONAL ADD PRIMARY KEY (ID);

CREATE TABLE INFO_PROFESIONAL AS SELECT ID,TEAM,COUNTRY,CATEGORY FROM TOUR;
ALTER TABLE INFO_PROFESIONAL ADD PRIMARY KEY (ID);

--3)
CREATE VIEW TOUR_VIEW AS SELECT * FROM TOUR;
--SELECT * FROM TOUR_VIEW;

--4)

CREATE OR REPLACE TRIGGER tr_inserta_ciclista
INSTEAD OF INSERT ON TOUR_VIEW
FOR EACH ROW
BEGIN
    INSERT INTO INFO_PERSONAL(ID,NAME,NATIONALITY)
    SELECT ID,NAME,NATIONALITY
    FROM TOUR_VIEW;

    INSERT INTO INFO_PROFESIONAL(ID,TEAM,COUNTRY,CATEGORY)
    SELECT ID,TEAM,COUNTRY,CATEGORY
    FROM TOUR_VIEW;
END tr_inserta_ciclista;

--INSERT INTO TOUR_VIEW(ID,TEAM,COUNTRY,NAME,NATIONALITY,CATEGORY)
--    VALUES(220,'TEAM DANIAT', 'Spain', 'DANIEL CASTLE', 'ESP', 'WorldTour');

--5)
CREATE TABLE LOG_INSERCION (
    USUARIO VARCHAR2(20),
    FECHA DATE
);

CREATE OR REPLACE TRIGGER tr_inserta_old
AFTER INSERT ON TOUR
FOR EACH ROW
BEGIN
    INSERT INTO LOG_INSERCION(USUARIO,FECHA)
    VALUES(USER,SYSDATE);
END tr_inserta_old;