0

I have a parent table in MySQL as follows:

CREATE TABLE `parentTBL` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source_name` varchar(255) DEFAULT NULL,
  `loc1` smallint(5) unsigned DEFAULT NULL,
  `loc2` smallint(5) unsigned DEFAULT NULL,
  `extra_info` json DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

a child table:

CREATE TABLE `childTBL` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `source_names_id` int(10) unsigned NOT NULL,
  `locations_id` int(10) unsigned NOT NULL,
  `extra_info` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_childTBL1_idx` (`source_names_id`),
  KEY `fk_childTBL2_idx` (`locations_id`),
  CONSTRAINT `fk_childTBL1` FOREIGN KEY (`source_names_id`) REFERENCES `source_names` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_childTBL2` FOREIGN KEY (`locations_id`) REFERENCES `locations` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and these 2 tables:

CREATE TABLE `source_names` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `source_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `source_name_UNIQUE` (`source_name`)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=latin1;

CREATE TABLE `locations` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `loc1` smallint(5) unsigned DEFAULT NULL,
  `loc2` smallint(5) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `loc1` (`loc1`,`loc2`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=latin1;

I have a trigger for parentTBL table before insert like this:

CREATE DEFINER=`root`@`localhost` TRIGGER `myDB`.`parentTBL_BEFORE_INSERT` BEFORE INSERT ON `parentTBL` FOR EACH ROW
BEGIN
    INSERT IGNORE INTO source_names (source_name) VALUES (NEW.source_name);
    INSERT IGNORE INTO locations (loc1,loc2) VALUES (NEW.loc1,NEW.loc2);
    INSERT IGNORE INTO childTBL (source_names_id,locations_id,extra_info) VALUES (????,????,NEW.extra_info);
END

and I want to insert into childTBL and 2 other tables before inserting into parentTBL but I don't know what should I put in ???? fields in above trigger (third INSERT IGNORE. Move scroll to right)?

(It should be noted that I don't want to use SELECT because I have some millions records and fast insertion is important to me and also my problem is not solved with LAST_INSERT_ID() because it is possible that I need the source_names_id which is added before last_inserted in source_names table.)

morteza ali ahmadi
  • 435
  • 7
  • 15
  • Maybe you can create an auxiliary table to hold the last source_names_id and locations_id. Then you should increase these data so to be consistent with the real ids in respective tables. And, after you insert into source_names and locaitons you can select their ids from the auxiliary table. – Diabolus Mar 28 '18 at 06:52
  • Possible duplicate of [LAST\_INSERT\_ID() MySQL](https://stackoverflow.com/questions/3837990/last-insert-id-mysql) – P.Salmon Mar 28 '18 at 06:58
  • @Diabolus and P.Salmon thanks, but as I edited my post (last paragraph with **bold** statement) I can not use any of these solutions. – morteza ali ahmadi Mar 29 '18 at 04:26

0 Answers0