插入触发器后的MySQL得到自动递增的值，插入后更新字段值给出“未知列".错误 [英] MySQL after insert trigger get auto incremed value, update field value after insert gives "Unknown column" error

问题描述

我试图找出一个触发器，将插入时自动生成的自动递增的"ID"主键字段的值分配给另一个字段"Sort_Placement"，以便插入后它们相同.

I am trying to figure out make a trigger to assign the value of the auto incremented 'ID' primary key field that is auto generated upon insert to another field 'Sort_Placement' so they are the same after insert.

如果您想知道为什么要这样做，可以将'Sort_Placement'用作表中的排序值，可以将其更改，但默认情况下，记录会添加到表的底部

If you are wondering why I am doing this, 'Sort_Placement' is used as a sort value in a table that can be changed but by default the record is added to the bottom of the table

表格数据

`ID` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`Account_Num` mediumint(8) unsigned NOT NULL,
`Product_Num` mediumint(8) unsigned NOT NULL,
`Sort_Placement` mediumint(8) unsigned DEFAULT NULL,
`Order_Qty_C` smallint(6) NOT NULL DEFAULT '0',
`Order_Qty_B` smallint(6) NOT NULL DEFAULT '0',
`Discount` decimal(6,2) NOT NULL DEFAULT '0.00',
PRIMARY KEY (`ID`),
UNIQUE KEY `ID_UNIQUE` (`ID`)

插入触发器后

CREATE 
TRIGGER `order_guide_insert_trigger`
AFTER INSERT ON `order_guide`
FOR EACH ROW
    BEGIN
    IF Sort_Placement IS NULL THEN
        SET Sort_Placement = NEW.ID;
    END IF;
END;

我尝试了很多使用"NEW"前缀的组合，但是没有碰到运气.例如，将NEW前缀放在每个字段名称之前.

I have tried a bunch of combinations of using the "NEW" prefix with no luck. For example putting the NEW prefix before each field name.

试用

INSERT INTO `order_guide` (`Account_Num`, `Product_Num`) VALUES ('5966', '3');

插入错误

ERROR 1054: Unknown column 'Sort_Placement' in 'field list'

推荐答案

这似乎有点繁琐，但是我能够使用MySQL内置的LAST_INSERT_ID()函数使其正常工作.

This seems like a bit of a hack job but I was able to get it working using the LAST_INSERT_ID() function built into MySQL.

CREATE TRIGGER `order_guide_insert_trigger`
BEFORE INSERT ON `order_guide`
FOR EACH ROW 
BEGIN
    IF NEW.Sort_Placement IS NULL THEN
        SET NEW.Sort_Placement = LAST_INSERT_ID() + 1;
    END IF;
END;

这也可行并且似乎可行

CREATE TRIGGER `order_guide_insert_trigger`
BEFORE INSERT ON `order_guide`
FOR EACH ROW 
BEGIN
    IF NEW.Sort_Placement IS NULL THEN
        SET NEW.Sort_Placement = (SELECT ID FROM order_Guide ORDER BY id DESC LIMIT 1) + 1;
    END IF;
END;

这篇关于插入触发器后的MySQL得到自动递增的值，插入后更新字段值给出“未知列".错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！