Refresh table column after inserting new record using MySQL triggers

Question

Imagine that I have a MySQL table (tbl_test) with these fields: id, title, priority .
id will automatically increase. I need to populate the priority field with a value equal to the id value after insertion.
Since I'm new to using MySQL triggers, please tell me what I have to write for this. I did something, but I think it is not:

CREATE TRIGGER 'test' AFTER INSERT ON `tbl_test` BEGIN SET new.priority = new.id; END 

Thank you for your help.

Answer 1

The way you are trying to set a value for a column is an update. Because you do this after the insert operation completes.

You really need a before trigger.

And in order to assign the same new automatically increasing value of the primary key column of the same table, it is better to get it from information_schema.tables .

An 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 you do not have a predefined trigger with the same name and / or action. If they are, then discard them before creating a new one.

Answer 2

I do not think you can do this. An AFTER INSERT trigger cannot modify the same table, either by issuing UPDATE, or 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 an error, for example

 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 similar to

 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)