update table column after insert new record using MySQL triggers
Solution 1
The way you are trying to set value to a column is an update. Because you are doing it after insert operation is completed.
You actually need a before
trigger.
And to assign the same new auto incremented value of primary key column of same table, you better get it from information_schema.tables
.
Example:
delimiter //
drop trigger if exists bi_table_name //
create trigger bi_table_name before insert on table_name
for each row begin
set @auto_id := ( SELECT AUTO_INCREMENT
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME='table_name'
AND TABLE_SCHEMA=DATABASE() );
set new.priority= @auto_id;
end;
//
delimiter ;
Note: Make sure that you don't have any pre-defined trigger with the same name and/or action. If have some, then drop them before creating the new.
Observations:
As per mysql documentation on last_insert_id(),
"if you insert multiple rows using a single INSERT statement,
LAST_INSERT_ID()
returns the value generated for the first inserted row only."
hence, depending on last_insert_id()
and auto_increment
field values in batch inserts seems not reliable.
Solution 2
I don't think you can do that. An AFTER INSERT trigger cannot modify the same table, neither by issuing an UPDATE nor by something like this:
DROP TRIGGER new_tbl_test;
DELIMITER $$
CREATE TRIGGER new_tbl_test
AFTER INSERT ON tbl_test for each row
begin
UPDATE tbl_test SET priority = new.id WHERE id = new.id;
END $$
DELIMITER ;
It gives error like
ERROR 1442 (HY000): Can't update table 'tbl_test' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
What you can do, is use a transaction:
Example : Table structure is like below
mysql> show create table tbl_test\G
*************************** 1. row ***************************
Table: tbl_test
Create Table: CREATE TABLE `tbl_test` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`title` char(30) DEFAULT NULL,
`priority` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Transaction
START TRANSACTION ;
INSERT INTO tbl_test (title)
VALUES ('Dr');
UPDATE tbl_test
SET `priority` = id
WHERE id = LAST_INSERT_ID();
COMMIT ;
Check data
mysql> SELECT * FROM tbl_test;
+----+-------+----------+
| ID | title | priority |
+----+-------+----------+
| 1 | Dr | 1 |
+----+-------+----------+
1 row in set (0.00 sec)
Mohammad Saberi
Updated on August 06, 2022Comments
-
Mohammad Saberi over 1 year
Imagine I have a MySQL table (tbl_test) with these fields: id, title, priority.
id will be incremented automatically. I need to fill priority field with a value as same as id field after inserting.
As I'm new in using MySQL triggers, please tell me what I have to write for it. I did something , but I think it is not true:CREATE TRIGGER 'test' AFTER INSERT ON `tbl_test` BEGIN SET new.priority = new.id; END
Thanks for your assistance.
-
Ravinder Reddy about 10 yearsMust be a
;
missing after end of firstset ...
statement. Add it. -
Mohammad Saberi about 10 yearsI would like to to it using Triggers
-
Ravinder Reddy about 10 yearsIt says syntax error is near (or before)
for each ..
. Can you please add your complete modified trigger definition to your post? -
Ravinder Reddy about 10 yearsAnd, I doubt that you forgot to add
create trigger ...
, first line from my example, which is a must to define a trigger. -
Mohammad Saberi about 10 yearsI'm working with Navicat for MySQL. So it will add
create trigger ...
itself. I have to write other codes only -
Khosro over 9 yearsDoes this triggers is thread-safe?I mean if two concurrent row inserted into table,it does not cause the same auto-increment value for two inserted record or inconsistent value for priority column?
-
DJDave about 9 yearsThis works (nice job) but I needed "delimiter $$" before "create trigger" to get around the syntax errors (and "end;" becomes "end$$")
-
Ravinder Reddy about 9 years@user1280840: Answer I posted has no errors. And the delimiter need not be
'$$'
but can be anything like in my answer'//'
. I useddelimiter //
beforecreate trigger
and closed it asend; //
. Hence no meaning in saying 'but I needed ....$$....' ... -
Ellert van Koperen about 9 yearsTo avoid problems i usualy put the delimiter after the drop command. For the rest, this post put me on the right track, thank you Ravinder!
-
Ravinder Reddy over 5 years@cmcdragonkai: you mean batch inserts? what were your observations??
-
CMCDragonkai over 5 yearsThe id is not updated for each record. It only works for the first record.
-
Ravinder Reddy over 5 yearsI am not sure how you verified it. but look into different example on each row here stackoverflow.com/a/23649301/767881
-
marksiemers almost 5 yearsBased on my testing, @CMCDragonkai is correct, this does not work with batch processing. To test, add a unique constraint to the
priority
column, and do an insert with multiple sets of values. In my testing, the unique constraint stops the insert 100% of the time. -
Richard A Quadling over 4 yearsMysql will only update the AUTO_INCREMENT value after the bulk insert is done. The same if you attempt to use DEFAULT CURRENT_TIMESTAMP(6). If the column receiving the value has a unique constraint, you cannot use the current table. The best solution I've seen so far is to replicate the ROWNUMBER functionality from MSSQL. A separate table that is only an autoincrement column. You insert, get LAST_INSERT_ID(), delete row, return value. But even with this, I'm not 100% sure LAST_INSERT_ID will return the row I just inserted when we are parallel bulk inserting.
-
Ravinder Reddy over 4 years@RichardAQuadling: As per mysql documentation on last_insert_id(), "if you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only."
-
Ravinder Reddy over 4 yearshence, depending on
last_insert_id()
andauto_increment
field values in batch inserts seems not reliable.