CREATE TRIGGER test1 AFTER INSERT ON `course_metadata_31`
FOR EACH ROW BEGIN
UPDATE `course_metadata_31`
SET `articleID` = `articleID` + 1
END;
I am using this approach. When I will insert new entry in course_metadata_31 it should increment articleID too. Because I want articleID as another auto increment column.
What should I do?
No idea why you need two columns auto incrementing values, there is no point... but if you insist -
You can accomplish it in a UDF or SP this way you have multiple columns auto incrementing a value.
EXAMPLE #1: STORED PROCEDURE (SP)
Table
CREATE TABLE tests (
test_id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
test_num INT(10) NULL,
test_name VARCHAR(10) NOT NULL
);
Stored Procedure
DELIMITER $$
CREATE PROCEDURE autoInc (name VARCHAR(10))
BEGIN
DECLARE getCount INT(10);
SET getCount = (
SELECT COUNT(test_num)
FROM tests) + 1;
INSERT INTO tests (test_num, test_name)
VALUES (getCount, name);
END$$
DELIMITER ;
Call the SP
CALL autoInc('one');
CALL autoInc('two');
CALL autoInc('three');
Look up the table
SELECT * FROM tests;
+---------+----------+-----------+
| test_id | test_num | test_name |
+---------+----------+-----------+
| 1 | 1 | one |
| 2 | 2 | two |
| 3 | 3 | three |
+---------+----------+-----------+
CREATE TABLE tests (
test_id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
test_num INT(10) NULL,
test_name VARCHAR(10) NOT NULL
);
User-defined Function
DELIMITER $$
CREATE FUNCTION autoInc ()
RETURNS INT(10)
BEGIN
DECLARE getCount INT(10);
SET getCount = (
SELECT COUNT(test_num)
FROM tests) + 1;
RETURN getCount;
END$$
DELIMITER ;
Insert using the UDF
INSERT INTO tests (test_num, test_name) VALUES (autoInc(), 'one');
INSERT INTO tests (test_num, test_name) VALUES (autoInc(), 'two');
INSERT INTO tests (test_num, test_name) VALUES (autoInc(), 'three');
Look up the table
SELECT * FROM tests;
+---------+----------+-----------+
| test_id | test_num | test_name |
+---------+----------+-----------+
| 1 | 1 | one |
| 2 | 2 | two |
| 3 | 3 | three |
+---------+----------+-----------+
These have been tested and verified. I'd personally use the function, it's more flexible.
Had the same problem. There is how i resolved it:
DELIMITER //
CREATE TRIGGER `estimate_before_insert` BEFORE INSERT ON `estimate` FOR EACH ROW BEGIN
DECLARE newNum INT DEFAULT 0;
SET newNum = (SELECT max(num) FROM estimate) + 1;
IF newNum IS NULL THEN
SET newNum = 1;
END IF;
SET NEW.num = newNum;
END//
DELIMITER ;
There are plenty of points, all of the people that posted otherwise just have not encountered a problem requiring it yet.... Here let me give an example
You have a customer table that your database users enter new customers into, under that you have some sub tables like address or positions, under those you have further sub tables to hold more specific data. NOW, lets say your users enter the same customer twice on accident and you need to merge all of the sub tables under 1 primary customer record....
Instead of wiping your table, reordering your sub tables or manually moving data over you can instead, link your sub tables to a key field that can be modified (even duplicated if you merge primary keys but thats for another lesson) Then have a second field that does not get modified when needing to merge. This gives you a primary AI key and a second 'changeable' field to tie your sub tables to...
Look up 'Merge linked primary and sub tables in mysql' and see the horrors that it would require a database admin to solve this simple problem..
My use case was to have to AI columns, one as a PK and another one to shuffle the order of items.
I thought of making a MySQL trigger to initialise the second AI column with the value of the first column so that I can shuffle it later. But turns out the after insert triggert in MySQL did not allow updation
I accomplished it very easily using Sequelize Hooks
You can use something similar to the following if you are working with Sequelize:
db.define('songs', { <your_model_definition> }, {
hooks: {
afterCreate: async (item, {}) => {
//id here is the PK which is Auto Increment (AI)
//order is the second key I want as AI
await item.update({ order: item.id});
console.log("afterCreate", item);
}
}
})
If you have two auto_increment columns they would be the same, so there is no point having two auto_increment columns.
Q: What should I do?
A: Review the reasons you think you need a second AUTO_INCREMENT columns, carefully consider what it is you are trying to achieve.
Come up with an alternate design that doesn't require you to add two AUTO_INCREMENT columns to a MySQL table.
If you do really need to have a second column with "auto increment" type behavior, one way to get that is to add a second dummy table with an auto_increment column, and use a BEFORE INSERT trigger to do an insert into the dummy table, and retrieve the id value that was inserted.
Something like this:
CREATE TABLE course_metadata_31_ai
( i INT UNSIGNED PRIMARY KEY AUTO_INCREMENT
);
DELIMITER $$
CREATE TRIGGER course_metadata_31_bi
BEFORE INSERT ON course_metadata_31
FOR EACH ROW
BEGIN
DECLARE lii INT;
IF ( NEW.article_id IS NULL OR NEW.article_id < 1 ) THEN
-- set article_id to auto_increment from dummy table
INSERT INTO course_metadata_31_ai (i) VALUES (NULL);
SELECT LAST_INSERT_ID() INTO lii;
SET NEW.article_id = lii;
-- DELETE FROM course_metadata_31_ai WHERE i < lii;
ELSE
-- set auto_increment col in dummy table to match a larger article_id
UPDATE course_metadata_31_ai t
JOIN ( SELECT MAX(r.i) AS i
FROM course_metadata_31_ai r
) s
ON s.i = t.i
SET t.i = GREATEST(t.i,NEW.article_id);
END IF;
END;
$$
DELIMITER ;
NOTE
You wouldn't necessarily have to delete rows from the dummy table, you wouldn't have to do it in the trigger, but there's no point in keeping them. You'd probably only really need to keep the row that has the largest auto_increment value, just as a prevention against the AUTO_INCREMENT from inadvertently being set lower with an ALTER TABLE statement.)
The IF ELSE in the trigger body is designed to emulate auto_increment behavior... if a value is supplied for article_id, use that, AND if it's larger than the current max value of auto_increment column in the dummy table, update that row in the dummy table to have the larger value (so the next insert that needs an auto_increment value will get the next higher value).
in the phpMyAdmin set the column property to AI its a tick box on the table structure. you dont even need to pass the details to the database
