1mU5dgUJ

1Insert data from a table into another table when a trigger fires
2DROP SCHEMA IF EXISTS tr;
3CREATE SCHEMA IF NOT EXISTS tr; 
4USE tr;
5CREATE TABLE IF NOT EXISTS pacienti
6( 
7   id_pac INT NOT NULL  AUTO_INCREMENT  PRIMARY KEY
8   Nume VARCHAR(40) DEFAULT  'anonim',
9   Prenume VARCHAR(40) DEFAULT  'anonim',
10   UNIQUE (Nume,Prenume)
11) ENGINE=INNODB;
12
13CREATE TABLE IF NOT EXISTS cabinete 
14(
15   id_cab INT  NOT NULL  AUTO_INCREMENT PRIMARY KEY
16   Denumire VARCHAR(40) DEFAULT  'nespecificat',
17   UNIQUE (Denumire)
18) ENGINE=INNODB;
19
20CREATE TABLE IF NOT EXISTS tmp 
21(
22   id_tmp INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
23   DataVizita VARCHAR(10),
24   OraIntrare TIME,
25   ComplDate DATETIME,
26   NumePacient VARCHAR(40) DEFAULT 'anonim',
27   PrenumePacient VARCHAR(40) DEFAULT 'anonim',
28   NumeMedic VARCHAR(40) DEFAULT 'nespecificat',
29   PrenumeMedic VARCHAR(40) DEFAULT 'nespecificat',
30   Cabinet VARCHAR(30) DEFAULT 'nespecificat'
31);
32
33CREATE TABLE IF NOT EXISTS vizite 
34(
35   id_viz INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY,
36   Data_viz DATETIME,
37   Medic_id INT(5) NOT NULL,
38   Pacient_id INT(5) NOT NULL,
39   Cabinet_id INT(5) NOT NULL, 
40   FOREIGN KEY (`Medic_id`) REFERENCES `tr`.`medici`(`id_med`) 
41   ON UPDATE RESTRICT ON DELETE CASCADE,
42   FOREIGN KEY (`Pacient_id`) REFERENCES `tr`.`pacienti`(`id_pac`)
43   ON UPDATE RESTRICT ON DELETE CASCADE,
44   FOREIGN KEY (`Cabinet_id`) REFERENCES `tr`.`cabinete`(`id_cab`)
45   ON UPDATE RESTRICT ON DELETE CASCADE
46) ENGINE=INNODB;
47	
48DataVizita | OraIntrare | NumePacient | PrenumePacient | NumeMedic | PrenumeMedic | Cabinet
49--------------------------------------------------------------------------------------------
5022/04/2005 | 01:52:34   | Huruiala    |Carmen Alexandra| Garbeata  |  Panait      |   1
5105/12/2005 | 04:16:02   | Schornig    |     Petre      | Rusu      | Dragos Mihai |   2
5207/11/2004 | 11:24:27   | Graur       |Alexandra-Cris. | Soarece   | Dan-Cristian |   4
5307/05/2008 | 06:35:44   | Rudnitchi   |     Marian     | Galescu   |George Valentin|  5
54 .
55 .
56 .
57 etc…
58	
59CREATE FUNCTION formdate(ziua VARCHAR(10), timpul TIME)RETURNS DATETIME       
60DERMINISTIC    
61RETURN 
62 CONCAT (DATE_FORMAT(STR_TO_DATE(ziua, '%d/%m/%Y' ) , '%Y-%m-%d' ),' ',timpul);
63
64DELIMITER $$
65CREATE TRIGGER upd_date BEFORE INSERT ON tmp
66FOR EACH ROW
67BEGIN
68      SET new.ComplDate = formdate(new.DataVizita,new.OraIntrare);
69END$$
70DELIMITER ;
71	
72DROP TRIGGER IF EXISTS upd_date;
73
74DELIMITER $$
75
76CREATE TRIGGER upd_date BEFORE INSERT ON tmp
77FOR EACH ROW 
78BEGIN
79
80  SET new.ComplDate=formdate(new.DataVizita,new.OraIntrare);
81
82  INSERT INTO pacienti(nume,prenume) VALUES(new.NumePacient,new.PrenumePacient)
83  ON DUPLICATE KEY UPDATE nume=new.NumePacient, prenume=new.PrenumePacient;
84
85  INSERT INTO cabinete(Denumire) VALUES(new.Cabinet)
86  ON DUPLICATE KEY UPDATE Denumire=new.Cabinet;
87
88
89END$$
90
91DELIMITER ;