MySQL after inserting the trigger receives an automatically incremented value, the value of the update field after insertion gives the error "Unknown column"

Question

I am trying to calculate a trigger to assign the value of a field with an additional key with automatic addition of "ID", which is automatically created when inserted into another field "Sort_Placement", so after the insert they match.

If you're wondering why I do this, "Sort_Placement" is used as the sort value in the table, which can be changed, but by default the record is added at 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 trigger 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 tried a bunch of combinations using the "NEW" prefix with no luck. For example, put the prefix NEW before each field name.

Try

 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 to work with a hack, but I was able to get it to work 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; 

It 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; 

Answer 2

I came across a similar (but different) requirement, where the field value in the table should have been based on the new Auto Increment ID. I found two solutions that worked for me.

The first option was to use an event timer that runs every 60 seconds. The event updated records in which my field was set to the default value of null. A good solution if you do not mind the 60 second delay (you can run it every 1 second if the field that is being updated is indexed). Basically this event does:

 CREATE EVENT `evt_fixerupper` ON SCHEDULE EVERY 1 MINUTE ENABLE COMMENT '' DO BEGIN UPDATE table_a SET table_a.other_field=CONCAT(table_a.id,'-kittens') WHERE ISNULL(table_a.other_field); END; 

Another option was to create your own unique primary identifiers (instead of relying on AUTOINCREMENT. In this case, I used the function (in my application) modeled after the perl module https://metacpan.org/pod/Data::Uniqid . Generated identifier has a huge length, but they work well, and I know the value before inserting, so I can use it to generate values ​​for additional fields.