MySQL trigger to update a field to the value of id
Solution 1
I don't know of any way to do this in one statement, even using a trigger.
The trigger solution that @Lucky suggested would look like this in MySQL:
CREATE TRIGGER MyTrigger BEFORE INSERT ON MyTable
FOR EACH ROW BEGIN
SET NEW.group_id = COALESCE(NEW.group_id, NEW.id);
END
However, there's a problem. In the BEFORE INSERT
phase, the auto-generated id
value hasn't been generated yet. So if group_id
is null, it defaults to NEW.id
which is always 0.
But if you change this trigger to fire during the AFTER INSERT
phase, so you have access to the generated value of NEW.id
, you can't modify column values.
MySQL doesn't support expressions for the DEFAULT
of a column, so you can't declare this behavior in the table definition either. *Update: MySQL 8.0.13 supports DEFAULT (<expression>)
but the expression still can't depend on an auto-increment value (this is documented).
The only solution is to do the INSERT
, and then immediately do an UPDATE
to change the group_id
if it's not set.
INSERT INTO MyTable (group_id, value) VALUES (NULL, 'a');
UPDATE MyTable SET group_id = COALESCE(group_id, id) WHERE id = LAST_INSERT_ID();
Solution 2
This works for me
DELIMITER $$
CREATE TRIGGER `myTriggerNameHere`
BEFORE INSERT ON `table` FOR EACH ROW
BEGIN
SET NEW.group_id = IF(NEW.group_id IS NULL, LAST_INSERT_ID()+1, NEW.group_id);
END;
$$
DELIMITER ;
Solution 3
I am answering here, as in the accepted answer Bill Karwin states:
In the BEFORE INSERT phase, the auto-generated id value hasn't been generated yet. So if group_id is null, it defaults to NEW.id which is always 0.
I have an answer for it - for OP (and the visitors to come), here are few points: You cannot update the table from where the trigger gets invoked, for it you'll get Error 1442:
Error Code: 1442
Can't update table 'MyTable' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
1.to Update the new row
Use BEFORE INSERT ON
trigger, this way you can update all the fields for the new row, which can be accessible via NEW operator i.e.
set NEW.group_id = NEW.id
2.Get auto_increment value before insert:
SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='MyTable'
To sum up - the trigger SQL for 'd be something as following:
DELIMITER //
DROP TRIGGER IF EXISTS MyTrigger//
CREATE TRIGGER MyTrigger BEFORE INSERT ON MyTable
FOR EACH ROW BEGIN
IF new.group_id IS NULL
set @auto_id := (SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='MyTable' AND TABLE_SCHEMA=DATABASE() );
set NEW.group_id = @auto_id;
ENF IF;
END;
//
DELIMITER ;
szeryf
Updated on December 02, 2020Comments
-
szeryf over 3 years
I would like to have a trigger to perform following operation for inserted records:
# pseudocode if new.group_id is null set new.group_id = new.id else # don't touch it end
More clearly: say I have one table with three columns:
id
primary key,group_id
int,value
varchar.When I insert with
group_id
like that:INSERT INTO table(value, group_id) VALUES ('a', 10)
I'd like to have:
id | group_id | value ---+----------+------ 1 | 10 | a
but when I omit
group_id
:INSERT INTO table(value) VALUES ('b')
it should be automatically set to the
id
of this record:id | group_id | value ---+----------+------ 2 | 2 | b
Is it possible with a trigger? (I know I can update the record after inserting but having the trigger would be nicer.)
-
Justin Giboney almost 15 yearsIf you tell me more about the structure of your tables, I can modify this query to work for you.
-
szeryf over 14 yearsDoes not work on MySQL: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE new.group_id IS NULL END' at line 1
-
Lucky over 14 yearsYou should be able to get the exact syntax from the examples at dev.mysql.com/doc/refman/5.1/en/create-trigger.html - I didn't test. I'll try an edit
-
szeryf over 11 yearsI'm sorry but you missed the point. It's not about inserting literal value
2
asgroup_id
. I want theid
of this record as thegroup_id
unless I specifygroup_id
explicitly. -
AA_PV over 7 yearsThat sucks. Even in 2016
-
Bill Karwin over 7 years@AA_PV, this could be solved by an independent sequence object, like they do in Oracle. This has been an open feature request in MySQL since 2003: bugs.mysql.com/bug.php?id=1625 But in Oracle, to use the sequence, you must call it either in application code, which means writing code for every INSERT to a table you want to have an auto-inc primary key. Or else write a trigger for every such table. MySQL users enjoy being able to skip writing so many triggers, and just declare
AUTO_INCREMENT
as a column option instead. -
AA_PV over 7 yearsyes but two AUTO_INCREMENT columns are not allowed in MySQL, right? Although I think something similar to stackoverflow.com/a/7345183/4411645 can be done. And to avoid the nested query, maybe it can be maintained in the application code as well, though that might be even more unsafe, not sure.
-
Bill Karwin over 7 years@AA_PV, correct, two AUTO_INCREMENT columns are not supported. But most users find this is not a problem.
-
ravo10 over 6 yearsIn an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated. Syntax So you can use
OLD.id
to access the ID. -
Bill Karwin over 6 years@ravo10, Please test that. I don't think
OLD.id
can be referenced in an INSERT trigger. -
Bill Karwin over 6 yearsSelecting the auto_increment value from the table introduces a race condition.
-
ravo10 over 6 years@BillKarwin Oh sorry. No, that is true! You can only use
OLD.id
forDELETE
andUPDATE
, but you need to useNEW.id
forINSERT
. I just made a system, and has been working fine until now. -
Richard A Quadling over 4 yearsThe NEW.id is not set and if group_id is unique, this will fail on the second row being inserted.
-
Richard A Quadling over 4 yearsDoesn't work for multi-row insert as the LAST_INSERT_ID is not updated until the batch is completed.