MySQL after insert trigger get auto incremed value, update field value after insert gives "Unknown column" error

Question

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.

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

Table Data

`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`)

After Insert Trigger

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;

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.

Trying it out

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

Insert Error

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

Answer 1

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;

This also works and seems to work

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;