我试图找出触发器来分配插入到另一个字段’Sort_Placement’时自动生成的自动递增的’ID’主键字段的值,因此插入后它们是相同的.

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

表数据

`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前缀.

尝试一下

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

插入错误

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

解决方法:

这似乎是一个黑客工作,但我能够使用MySQL内置的LAST_INSERT_ID()函数使其工作.

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;