update table column after insert new record using MySQL triggers
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.
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.
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.
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)
START TRANSACTION ; INSERT INTO tbl_test (title) VALUES ('Dr'); UPDATE tbl_test SET `priority` = id WHERE id = LAST_INSERT_ID(); COMMIT ;
mysql> SELECT * FROM tbl_test; +----+-------+----------+ | ID | title | priority | +----+-------+----------+ | 1 | Dr | 1 | +----+-------+----------+ 1 row in set (0.00 sec)