CODEWITHSUNDEEP
mysqltriggersauto-increment
Ivan
Ivan

Reputation: 31

How to change the auto_increment field in a table trigger in MySQL?

There is a necessity when inserting into a table of values to change the auto-increment field to another that no two of the same id in these tables. It is necessary for the data output from the third table based on the recording and going to not add to the table a column indicating. Here's my trigger, but it does not work

CREATE TRIGGER `update_id` AFTER INSERT ON `table1`
FOR EACH ROW BEGIN
ALTER TABLE `table2` AUTO_INCREMENT = NEW.id;
END;

Upvotes: 0

Views: 1113

Answers (1)

spencer7593
spencer7593

Reputation: 108470

It's not entirely clear what problem you are trying to solve.

But it sounds as if you have two tables with an id column, and you want to ensure that the same value of id is not used in both tables. That is, if id value 42 exists in table1, you want to ensure that 42 is not used as an id value in table2.

Unforunately, MySQL does not provide any declarative constraint for this.

It sounds as if you want an Oracle-style SEQUENCE object. And unfortunately, MySQL doesn't provide an equivalent.

But what we can do is emulate that. Create an extra "sequence" table that contains an AUTO_INCREMENT column. The purpose of this table is to be used to generate id values, and to keep track of the highest generated id value:

CREATE TABLE mysequence (id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY);

Then, we'd remove the AUTO_INCREMENT attribute from the id columns of the two tables we want to generate distinct id values for.

For those tables, we'd create BEFORE INSERT triggers that will obtain distinct id values and assign it to the id column. To generate a unique value, we can insert a row to the new mysequence table, and then retrieve the auto_increment value using the LAST_INSERT_ID function.

Something like this:

CREATE TRIGGER table1_bi
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
   DECLARE generated_id INT UNSIGNED;
   -- do we need to generate a value for id column?
   IF NEW.id IS NULL THEN
      -- generate unique id value with insert into sequence table
      INSERT INTO mysequence (id) VALUES (NULL);
      -- retrieve inserted id value 
      SELECT LAST_INSERT_ID() INTO generated_id;
      -- assign the retrieved value to the id columns of the row being inserted
      SET NEW.id = generated_id;
   END IF
END$$

(That's just a rough outline, likely there's at least one syntax error in there somewhere.)

You'd need to create a BEFORE INSERT trigger for each of the tables.

This is one approach to generating distinct values for the id columns.

Note that it wouldn't be necessary to keep ALL of the rows in the mysequence table, it's only necessary to keep the row with the largest id value.

Also note that this doesn't enforce any constraint on either tables; some session could supply a value for id that is already in the other table. To prevent that, the trigger could raise an error if a non-NULL id value is supplied. It might also be possible to allow non-NULL values, and to perform a query to check if the supplied id value already exists in the other table, and raise an error if it does. But that query would be subject to a race condition... two concurrent sessions doing inserts to the tables, and you'd need to implement some concurrency killing locking mechanisms to prevent concurrent inserts.

Upvotes: 1

Related Questions