CREATE TABLE pacienti (
    nume CHAR(50) NOT NULL,
    prenume CHAR(50) NOT NULL
)  CHARACTER SET utf8 COLLATE utf8_general_ci
ENGINE=INNODB;

CREATE TABLE cabinete (
    denumire CHAR(50) NOT NULL
)  CHARACTER SET utf8 COLLATE utf8_general_ci
ENGINE=INNODB;

CREATE TABLE vizite (
    datasiora DATETIME NOT NULL,
    idmedic INT NOT NULL,
    idpacient INT NOT NULL,
    idcabinet INT NOT NULL

) CHARACTER SET utf8 COLLATE utf8_general_ci
ENGINE=INNODB;

CREATE TABLE tabelaimport (
    DataVizita CHAR(10) NOT NULL,
    OraIntrare TIME NOT NULL,
    NumePacient CHAR(50) NOT NULL,
    PrenumePacient CHAR(50) NOT NULL,
    NumeMedic CHAR(50) NOT NULL,
    PrenumeMedic CHAR(50) NOT NULL,
    Cabinet CHAR(50) NOT NULL
)  CHARACTER SET utf8 COLLATE utf8_general_ci
ENGINE=INNODB;

LOAD DATA INFILE '/Proiect/vizite.txt'
INTO TABLE tabelaimport
    CHARSET utf8
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY 'n'
    IGNORE 1 lines
(DataVizita,OraIntrare,NumePacient,PrenumePacient,NumeMedic,PrenumeMedic,Cabinet);

DELIMITER $$
CREATE TRIGGER repartizaredate BEFORE INSERT  ON tabelaimport FOR EACH ROW
BEGIN

INSERT INTO pacienti (nume, prenume)
    SELECT new.Numepacient, tabelaimport.PrenumePacient
            FROM tabelaimport
            WHERE tabelaimport.NumePacient = new.NumePacient
            ;

END
$$