update table column after insert new record using MySQL triggers

Question

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.

I don't think you can do that. An `AFTER INSERT` trigger cannot modify the same table, neither by issuing an UPDATE — Abdul Manaf, Mar 12 '14 at 05:49

Ravinder Reddy · Answer 1 · 2019-11-02T06:25:13.193

20

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.

edited Nov 02 '19 at 06:25

answered Mar 12 '14 at 06:05

Ravinder Reddy

23,692
6
52
82

Must be a `;` missing after end of first `set ...` statement. Add it. – Ravinder Reddy Mar 12 '14 at 07:36
It says syntax error is near (or before) `for each ..`. Can you please add your complete modified trigger definition to your post? – Ravinder Reddy Mar 12 '14 at 08:13
And, I doubt that you forgot to add `create trigger ...`, first line from my example, which is a must to define a trigger. – Ravinder Reddy Mar 12 '14 at 08:15
I'm working with Navicat for MySQL. So it will add `create trigger ...` itself. I have to write other codes only – Mohammad Saberi Mar 12 '14 at 09:00
Does 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? – Khosro Jan 27 '15 at 08:01
This works (nice job) but I needed "delimiter $$" before "create trigger" to get around the syntax errors (and "end;" becomes "end$$") – DJDave Feb 27 '15 at 10:34
@user1280840: Answer I posted has no errors. And the delimiter need not be `'$$'` but can be anything like in my answer `'//'`. I used `delimiter //` before `create trigger` and closed it as `end; //`. Hence no meaning in saying '*but I needed ....$$....*' ... – Ravinder Reddy Feb 28 '15 at 06:50
To 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! – Ellert van Koperen Mar 16 '15 at 21:21
@cmcdragonkai: you mean batch inserts? what were your observations?? – Ravinder Reddy Aug 03 '18 at 18:46
2

The id is not updated for each record. It only works for the first record. – CMCDragonkai Aug 04 '18 at 02:27
I am not sure how you verified it. but look into different example on each row here https://stackoverflow.com/a/23649301/767881 – Ravinder Reddy Aug 04 '18 at 05:41
1

Based 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. – marksiemers May 23 '19 at 00:17
Mysql 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. – Richard A Quadling Nov 01 '19 at 10:05
@RichardAQuadling: As per [mysql documentation on **last_insert_id()**](https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_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 Nov 02 '19 at 06:13
hence, depending on `last_insert_id()` and `auto_increment` field values in batch inserts seems not reliable. – Ravinder Reddy Nov 02 '19 at 06:15

score 3 · Answer 2 · answered Mar 12 '14 at 05:56

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)

I would like to to it using Triggers – Mohammad Saberi Mar 12 '14 at 08:03 — Mohammad Saberi, Mar 12 '14 at 08:03

Richard A Quadling · Answer 3 · 2019-11-02T19:29:45.270

My solution. I initially wanted the table's uniqueID in a navOrder column. But the issues of getting the uniqueID of the table in question whilst in a trigger for a bulk insert was too problematic. So I built a mechanism similar to MSSQL's ROWNUMBER feature where every row is numbered, regardless of the table or schema that the row is in. Whilst the solution I've built below doesn't generate a number for every table (it could if the triggers were added to every table), it solves my problem of needing a unique value for every row.

So, I have 2 tables, each with a BEFORE INSERT trigger that calls upon a User Defined Function (UDF) that will get the next unique sequential number.

I've bulk tested the functionality (inserting 1,000 rows in 1 query, running 1,000 queries, running all that 10 times in parallel) and we use this for a site that experiences around 2,000 real time users a minute and approximately 15,000 inserts a minute. Not a Facebook, but all we use this and it is working for us.

If you run the code below, you'll see that rolling back does NOT rollback the sequential number.

We get no deadlocks and no duplicate values (as the unique constraint on the navOrder columns does not allow duplicates).

For me this is a relatively easy to understand solution.

CREATE SCHEMA TestLastInsertId;
USE TestLastInsertId;

CREATE TABLE Table1 (
    `tempID`    INT(11)        NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `item`      VARCHAR(256)   NOT NULL,
    `navOrder`  INT(11) UNIQUE NOT NULL,
    `createdAt` TIMESTAMP(6)   NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    `updatedAt` TIMESTAMP(6)   NULL     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci AUTO_INCREMENT = 1;

CREATE TABLE Table2 (
    `tempID`    INT(11)        NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `item`      VARCHAR(256)   NOT NULL,
    `navOrder`  INT(11) UNIQUE NOT NULL,
    `createdAt` TIMESTAMP(6)   NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
    `updatedAt` TIMESTAMP(6)   NULL     DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP(6)
) ENGINE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_unicode_ci AUTO_INCREMENT = 1;

CREATE TABLE IF NOT EXISTS `nav_order_sequence` (
    `navOrderSequence` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE = InnoDB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8;

DELIMITER ;;

CREATE FUNCTION getNextNavOrder()
  RETURNS INT(11) LANGUAGE SQL NOT DETERMINISTIC MODIFIES SQL DATA SQL SECURITY INVOKER
  BEGIN
    INSERT INTO nav_order_sequence() VALUES();
    SET @navOrder = LAST_INSERT_ID();
    DELETE FROM nav_order_sequence WHERE navOrderSequence = @navOrder;
    RETURN @navOrder;
  END;;

CREATE TRIGGER Table1_BEFORE_INSERT BEFORE INSERT ON Table1 FOR EACH ROW
BEGIN
SET NEW.navOrder = getNextNavOrder();
END;;

CREATE TRIGGER Table2_BEFORE_INSERT BEFORE INSERT ON Table2 FOR EACH ROW
BEGIN
SET NEW.navOrder = getNextNavOrder();
END;;

DELIMITER ;

INSERT INTO Table1(item) VALUES('Item1'),('Item2'),('Item3');
INSERT INTO Table2(item) VALUES('Item4'),('Item5'),('Item6');
SELECT * FROM Table1; -- Result 1
SELECT * FROM Table2; -- Result 2
BEGIN;
INSERT INTO Table1(item) VALUES('Item7'),('Item8'),('Item9');
INSERT INTO Table2(item) VALUES('Item10'),('Item11'),('Item12');
SELECT * FROM Table1; -- Result 3
SELECT * FROM Table2; -- Result 4
ROLLBACK;
INSERT INTO Table1(item) VALUES('Item13'),('Item14'),('Item15');
INSERT INTO Table2(item) VALUES('Item16'),('Item17'),('Item18');
SELECT * FROM Table1; -- Result 5
SELECT * FROM Table2; -- Result 6

DROP SCHEMA TestLastInsertId;

Result 1 - Add 3 rows to Table 1 - navOrders 1, 2, and 3.
1   Item1   1   2019-11-02 18:58:28.657690  
2   Item2   2   2019-11-02 18:58:28.657690  
3   Item3   3   2019-11-02 18:58:28.657690

Result 2 - Add 3 rows to Table 2 - navOrders 4, 5, and 6.
1   Item4   4   2019-11-02 18:58:28.669873  
2   Item5   5   2019-11-02 18:58:28.669873  
3   Item6   6   2019-11-02 18:58:28.669873

Result 3 - Add 3 more rows to Table 1 - navOrders 7, 8, and 9.
1   Item1   1   2019-11-02 18:58:28.657690  
2   Item2   2   2019-11-02 18:58:28.657690  
3   Item3   3   2019-11-02 18:58:28.657690  
4   Item7   7   2019-11-02 18:58:28.704766  
5   Item8   8   2019-11-02 18:58:28.704766  
6   Item9   9   2019-11-02 18:58:28.704766

Result 4 - Add 3 more rows to Table 2 - navOrders 10, 11, and 12.
1   Item4   4   2019-11-02 18:58:28.669873  
2   Item5   5   2019-11-02 18:58:28.669873  
3   Item6   6   2019-11-02 18:58:28.669873  
4   Item10  10  2019-11-02 18:58:28.706930  
5   Item11  11  2019-11-02 18:58:28.706930  
6   Item12  12  2019-11-02 18:58:28.706930

A rollback happened here, so rows 4, 5, and 6 of both tables are removed.

Result 5 - Add 3 more rows to Table 1 after a rollback - navOrders 13, 14, and 15.
1   Item1   1   2019-11-02 18:58:28.657690  
2   Item2   2   2019-11-02 18:58:28.657690  
3   Item3   3   2019-11-02 18:58:28.657690  
7   Item13  13  2019-11-02 18:58:28.727303  
8   Item14  14  2019-11-02 18:58:28.727303  
9   Item15  15  2019-11-02 18:58:28.727303

Result 6 - Add 3 more rows to Table 1 after a rollback - navOrders 16, 17, and 18.
1   Item4   4   2019-11-02 18:58:28.669873  
2   Item5   5   2019-11-02 18:58:28.669873  
3   Item6   6   2019-11-02 18:58:28.669873  
7   Item16  16  2019-11-02 18:58:28.730307  
8   Item17  17  2019-11-02 18:58:28.730307  
9   Item18  18  2019-11-02 18:58:28.730307

If you were to remove the UNIQUE constraint on navOrder and replace the function called in the triggers to be LAST_INSERT_ID(), you'll see the duplicate values.

score 0 · Answer 4 · answered Aug 22 '23 at 06:20

Since:

MySQL can't give you the auto increment ID for a new row inside BEFORE INSERT.
MySQL will not allow you to update the row you're inserting inside AFTER INSERT.

A solution is to:

Create a duplicate "staging" version of your table. You insert rows into this table and MySQL generates the IDs for you.
Create a trigger on that table that automatically inserts rows (with the generated IDs) into the table you actually want to use.

CREATE TABLE `A` (
    `id` INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
    `foo` TEXT
);

CREATE TABLE `B` (
    `id` INTEGER PRIMARY KEY NOT NULL,
    `foo` TEXT,
    `bar` TEXT
);

DELIMITER //
CREATE TRIGGER `insert_row_into_b`
AFTER INSERT ON `A`
FOR EACH ROW
BEGIN
    INSERT INTO `B` (`id`, `foo`, `bar`)
    VALUES (
        `NEW`.`id`,
        `NEW`.`foo`,
        CONCAT('New Value - ', `NEW`.`foo`)
    );
END//
DELIMITER ;

INSERT INTO `A` (`foo`) VALUES ('test');

SELECT * FROM `B`;

Note: you can't create a trigger on B that updates A (going full circle), otherwise you'll run into the ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG error ("Can't update table '%s' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.").

update table column after insert new record using MySQL triggers

4 Answers4

Linked