insert foreign keys with trigger

Hello.
I got the following tables :

CREATE TABLE A (
id_a INT AUTO_INCREMENT PRIMARY KEY,
name_a VARCHAR(50),
surname_a VARCHAR(50));

CREATE TABLE B(
id_b INT AUTO_INCREMENT PRIMARY KEY,
name_b VARCHAR(50),
surname_b VARCHAR(50),
unique bbb(name_b,surname_b));

CREATE TABLE C(
id_c INT AUTO_INCREMENT PRIMARY KEY,
cab VARCHAR(40) UNIQUE);

CREATE TABLE D(
id_as INT,
id_bs INT,
id_cs INT,
dt DATETIME,
FOREIGN KEY(id_as) REFERENCES A(id_a),
FOREIGN KEY(id_bs) REFERENCES B(id_b),
FOREIGN KEY(id_cs) REFERENCES C(id_c));

CREATE TABLE E(
dat DATE,
ora TIME,
name_b VARCHAR(50),
surname_b VARCHAR(50),
name_a VARCHAR(50),
surname_a VARCHAR(50),
cab VARCHAR(40));

Table A and table E are populated with a load file process. I am using a trigger on table E to populate tables B, C and D. The trigger has a continue handler for duplicate entries. I managed so far to populate tables B and C but I have REAL trouble on doing the same with table D. I can't find a way to get the primary keys into that table. I've tried to get them into a variable by using the handler's duplicate flag but is not working. Any ideas ? Any help would be highly appreciated. Thank you.

 

I get a little bit confused, but are all these tables necessary? I'm just wondering what the purpose of the tables are. You seem to keep the same values in multiple locations (unless I'm misunderstanding and there is one table for something uploaded, and another for downloaded or something like that?) which is never a good idea. Also, keeping names (first and last) to varchar(50) seems to me to be a possible pitfall. Yes, there aren't that many people using their full name if it's over 100 characters total, but sometimes people have multiple first names, for instance, and that can quickly pass 50 characters. Not a big concern, just a potential problem.

 

Thank you for your post. This is a project I am working on thus all these tables are necessary. This is not the exact structure of the tables ( column names and length) because its already a long post and I didn't want to make it even longer but to be precise : table A contains a list of doctors, table B a list of patients table C a list of cabinets table D a list with visit days and hours and the foreign key and table E has doctors, patients, cabinets and the visit information. I've been wraping my head for 2 days trying to solve this foreign key problem but I just can't do it.

 

Okay, so it's basically a many-to-many database.

However, if the structure is as you has stated, it could probably be minimized a bit.

Table A:
Doctor information
id, name, surname

Table B:
Patient information
id, name, surname (why do you have the complete name here?)

Table C:
Cabinet information
id, cab

Table D:
The visit table - I'm not sure why you're making this so complicated?
id, doctor, patient, cabinet and datetime
populated by auto-increase primary key, doctor_id, patient_id, cabinet_id and datetime

Table E:
id, datetime, doctor, patient,cabinet
populated by auto-increase primary key, datetime from table d, doctor_id, patient_id and cabinet_id
(however, that last table could be skipped completely, and basically just getting the information based on a joins when pulling the data)

I think that would be both quicker and easier, since you're just using the IDs from the other tables, and you're not storing data multiple times - you will, of course, when pulling from Table E, have to do a join on the other tables to pull the information you need from those, so you actually get the names, not just an id. Joins ARE more costly, they are, but unless you're planning on pulling thousands of records, this shouldn't really be that big a problem?

And what is the need for the foreign key restraints in Table D?

 

I can not change anything except delete table E after the other tables are populated.

 

How does the trigger look that you currently have? The problem is probably that you do not have direct access to the IDs you need to populate the foreign key fields from Table E (where the trigger is located, if I'm not mistaken) - what you need is a multilayer trigger, which inputs the data into the other tables, then pulls those NEW.id_X and puts them in the foreign keys columns.

 

thank you for not losing interest for my problem. tomorrow is my deadline. the trigger looks like this:

delimiter &&


CREATE trigger tabint after insert on E
   
for each row
begin
   
declare x int default 0;
   
declare y,z int(5);
   
declare continue handler for 1062
       
begin
           
    set x=1;
   
end;
       
       
insert into B(name_b, surname_b) select new.name_b, new.surname_b;   
if x=1 then set y=(select id_b from B);       
else set y=(last_insert_id());     
end if;       
insert into C(cab) select new.cab;       
if x=1 then set z=(select id_c from C);   
else set z=(last_insert_id());    
end if;
end&&

Code (SQL):

it may not be corect...i'm really new at MySQL

 

Well, that trigger is not complete.
You'll have to insert the actual values you need in Table D as well?
And you'll need to pull the last insert id - currently, all you're doing is pull all the ids from the different tables you've inserted into, I think

Basically, after both of those inserts, when you have values for both x and y just do a new insert into the last table? Shouldn't that work?

 

Well, this trigger is not good because I cant insert a result set into a variable(y) . But I think this is part of the solution, using the handler's variable(x).
When x = 1 it means that there is a duplicate entry and the only way to get the id is with select, when x = 0 it means that there is not a duplicate entry and I should be able to get the id with last_insert_id(). I don't know how to write it. At this point this is to much for me.
I know the trigger is not complet but I don't know how to complet it .

 

is this problem hard ? 408 views and no answer

 

I finally got it. I don't know if you guys are interested but here it goes :

DELIMITER &&

CREATE trigger mytrigger before insert on E for each row

begin

declare x,y,z,double int;
declare dou_reg condition for 1062;

declare continue handler for dou_reg 
  begin
set double = 0;
  end;

set double = 1;

  set foreign_key_checks = 0;

insert into A(name_a, surname_a) values(new.name_a, new.surname_a);

set x= last_insert_id();

    if double = 0 then
       select distinct id_a from A where name_a=new.name_a and surname_a=new.surname_a into x;
     set double = 1;
    end if;

 
insert into B(name_b, surname_b) values (new.name_b, new.surname_b);

set y= last_insert_id();

    if double = 0 then
       select distinct id_b from B where name_b=new.name_b and surname_b=new.surname_b into y;
     set double = 1;
    end if;

insert into C (cab) values (new.cab);

set z= last_insert_id();

     if double = 0 then
        select distinct id_c from C where cab=new.cab into z;
       set double = 1;
     end if;

insert into D (id_as, id_bs, id_cs, dt) values (x,y,z,(concat(dat,' ',ora)));

SET FOREIGN_KEY_CHECKS=1;

end&&

Code (markup):