使用MySQL触发器插入新记录后更新表列

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:

想象一下，我有一个MySQL表（tbl_test），其中包含以下字段：id，title，priority。 id将自动递增。我需要在插入后使用与id字段相同的值填充优先级字段。由于我是使用MySQL触发器的新手，请告诉我我要为它编写的内容。我做了一些事情，但我认为这不是真的：

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

Thanks for your assistance.

感谢你的协助。

2 个解决方案

#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.

并且要为同一个表分配相同的新的自动递增的主键列值，最好从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.

注意：确保没有任何具有相同名称和/或操作的预定义触发器。如果有一些，则在创建新的之前删除它们。

#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:

我认为你不能这样做。 AFTER INSERT触发器不能通过发出UPDATE或类似的东西来修改同一个表：

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)

#1