Confused on Triggers and Store Procedures
I've chosen to post this in stored procedures because I believe this is what I need to solve my problem. I'm new to SQL and looking for help. I want to create a record in one of my tables; barn, pen or incubator and have a record created in my mapping table, location, then put that id into the record that was originally created. I think one of the reasons I'm having problems finding a solution, is my inability to properly frame my question.
Here's my design, (I'm not married to it if there are better ways) for an inventory system. I have three different location types; barn, pen and incubator. My inventory will have a contain and ID held in the mapping table, location. I use the mapping table to determine the ID and the type of the location, then use the relation between the mapping table and my three location type tables, to get the actual location name and any other values associated with that location type. (For now, my location entities hold simple data.)
-- -----------------------------------------------------
-- Table `waproc`.`barn`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `waproc`.`barn` ;
CREATE TABLE IF NOT EXISTS `waproc`.`barn` (
`id` INT(6) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`year_built` DATE NOT NULL,
`location_id` INT(7) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `barn_name_UNIQUE` (`name` ASC),
INDEX `fk_barn_location_id__location_id_idx` (`location_id` ASC),
CONSTRAINT `fk_barn_location_id__location_id`
FOREIGN KEY (`location_id`)
REFERENCES `waproc`.`location` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `waproc`.`incubator`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `waproc`.`incubator` ;
CREATE TABLE IF NOT EXISTS `waproc`.`incubator` (
`id` INT(7) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`location_id` INT(7) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `name_UNIQUE` (`name` ASC),
INDEX `fk_incubator_location_id__location_id_idx` (`location_id` ASC),
CONSTRAINT `fk_incubator_location_id__location_id`
FOREIGN KEY (`location_id`)
REFERENCES `waproc`.`location` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `waproc`.`pen`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `waproc`.`pen` ;
CREATE TABLE IF NOT EXISTS `waproc`.`pen` (
`id` INT(7) UNSIGNED ZEROFILL NOT NULL,
`name` VARCHAR(45) NOT NULL,
`location_id` INT(7) UNSIGNED NULL DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX `fk_pen_location_id__location_id_idx` (`location_id` ASC),
CONSTRAINT `fk_pen_location_id__location_id`
FOREIGN KEY (`location_id`)
REFERENCES `waproc`.`location` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;
-- -----------------------------------------------------
-- Table `waproc`.`location`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `waproc`.`location` ;
CREATE TABLE IF NOT EXISTS `waproc`.`location` (
`id` INT(7) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
`type` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `name_UNIQUE` (`name` ASC))
ENGINE = InnoDB
AUTO_INCREMENT = 0
DEFAULT CHARACTER SET = utf8;
An example: When I create a record in waproc.pen, I simply create a pen name. The system generates an autoincremented primary key and leaves the waproc.pen.location_id NULL. Now I want to create a autoincremented, unique record in waproc.location with a waproc.location.name value of "PEN". I want to get the value of waproc.location.id for this newly created record, and update the value of waproc.pen.location_id = waproc.location.id.
I've tried with a trigger on waproc.pen and I can create the waproc.location record properly this way. However, I can't get the waproc.location.id value into waproc.pen.location_id using this method. Here's my trigger on pen_before_insert:
CREATE
DEFINER=`root`@`localhost`
TRIGGER `wapro`.`pen_BEFORE_INSERT`
BEFORE INSERT ON `waproc`.`pen`
FOR EACH ROW
BEGIN
SET @loctype = 'PEN';
INSERT INTO location (id, type) VALUES (NEW.id, @loctype);
SET @locID = (select max(location.id) from location where location.type = "PEN");
SET @penID = (select max(pen.id) from pen);
UPDATE pen SET location.id = @locID WHERE pen.id = @penID;
END
This fails but shows the idea of what I'm trying to accomplish - probably better than long drawn-out text explanations.
I'm simply too new to SQL to find a way to make this work. Seems to me I should have a SP that does all this but I don't understand how to make this work without using a trigger.
Hopefully someone can point me in the right direction.
Thanks,
Bart