I am trying to implement Table with multiple incrementing columns which doesn't reuse deleted column values. This post was tagged as already being answered by How auto-increment within a subset of the table MYSQL, however, the referenced post did not meet the stated requirements as it allows the subset incremented keys to be duplicated. In the first post, a comment was given:
Create a table that will store last AI numbers per type. Use a trigger
to increment it on every insert and copy to the original table. – Paul
Spiegel
I thought this was a great idea, and implemented it.
-- MySQL Script generated by MySQL Workbench
-- 02/19/17 08:53:34
-- Model: New Model Version: 1.0
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;
-- -----------------------------------------------------
-- Table `mydb`.`t1`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t1` (
`pk1` INT NOT NULL,
`pk2` INT NOT NULL,
`id2` INT NOT NULL DEFAULT 0,
PRIMARY KEY (`pk1`, `pk2`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`t2`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`t2` (
`id` INT NOT NULL AUTO_INCREMENT,
`id2` INT NOT NULL,
PRIMARY KEY (`id`))
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
USE `mydb`;
DELIMITER $$
USE `mydb`$$
CREATE TRIGGER `t2_BINS` BEFORE INSERT ON `t2` FOR EACH ROW
begin
UPDATE t1 SET id2=id2+1 WHERE pk1=NEW.pk1 AND pk2=NEW.pk2;
SET NEW.id2=(SELECT id2 FROM t1 WHERE pk1=NEW.pk1 AND pk2=NEW.pk2);
end$$
DELIMITER ;
When executing the script, however, I receive the following error:
ERROR 1235 (42000) at line 68: This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
I am using MySQL 5.5.54. Are there new versions which can do so?
Are there any workarounds to accomplish this?
You have probably defined two BEFORE INSERT triggers on one table. At least that's what the error message says. Uncomment the second trigger in this demo and you will get the same error message
This version of MySQL doesn't yet support 'multiple triggers
with the same action time and event for one table'
The second mistake is that you have defined the trigger on the wrong table (or you have mixed up the tables in the trigger). Your trigger is on t2, but you are trying to acces columns from t1 (NEW.pk1). So you would get the following error message:
Unknown column 'pk1' in 'NEW'
http://sqlfiddle.com/#!9/1515abb (Uncomment the lines in the trigger.)
However - even fixing that, the logic doesn't look correct. A working version of what you might have intended could be:
CREATE TABLE IF NOT EXISTS `t1` (
`pk1` INT NOT NULL,
`pk2` INT NOT NULL DEFAULT 0,
PRIMARY KEY (`pk1`, `pk2`))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `t2` (
`pk1` INT NOT NULL,
`last_pk2` INT NOT NULL,
PRIMARY KEY (`pk1`))
ENGINE = InnoDB;
DELIMITER //
CREATE TRIGGER `t1_BINS` BEFORE INSERT ON `t1` FOR EACH ROW
begin
UPDATE t2 SET last_pk2=last_pk2 + 1 WHERE pk1=NEW.pk1;
SET NEW.pk2=(SELECT last_pk2 FROM t2 WHERE pk1=NEW.pk1);
end//
DELIMITER ;
As you can see in the demo, the second insert doesn't specify pk2, but it is increased in the result.
This however will fail, if you insert a pk1 value that is not registered in t2. (Uncomment last line in the demo) In this case a new row has to be inserted into t2. Since pk1 is PRIMARY KEY in t2 we can use INSERT .. ON DUPLICATE KEY UPDATE .. syntax:
CREATE TRIGGER `t1_BINS` BEFORE INSERT ON `t1` FOR EACH ROW
begin
INSERT INTO t2 (pk1, last_pk2) values (NEW.pk1, 1)
ON DUPLICATE KEY UPDATE last_pk2 = last_pk2 + 1;
SET NEW.pk2=(SELECT last_pk2 FROM t2 WHERE pk1=NEW.pk1);
end//
http://sqlfiddle.com/#!9/79eeea/1
Now last step is to make it concurrency save. We must prevent two concurrent threads/sessions to read the same value for t2.last_pk2. Thus writing and reading should be done in one statement. We can use a session variable or LAST_INSERT_ID() to do that:
CREATE TRIGGER `t1_BINS` BEFORE INSERT ON `t1` FOR EACH ROW
begin
INSERT INTO t2 (pk1, last_pk2) values (NEW.pk1, @last_pk2 := 1)
ON DUPLICATE KEY UPDATE last_pk2 = @last_pk2 := (last_pk2 + 1);
SET NEW.pk2 = @last_pk2;
end//
http://sqlfiddle.com/#!9/dc235a/1
CREATE TRIGGER `t1_BINS` BEFORE INSERT ON `t1` FOR EACH ROW
begin
INSERT INTO t2 (pk1, last_pk2) values (NEW.pk1, LAST_INSERT_ID(1))
ON DUPLICATE KEY UPDATE last_pk2 = LAST_INSERT_ID(last_pk2 + 1);
SET NEW.pk2 = LAST_INSERT_ID();
end//
http://sqlfiddle.com/#!9/86ed740/1
