I have a table that has a Sequence number. This sequence number will change and referencing the auto number will not work. I fear that the values of the trigger will collide. If two transactions read at the same time.

I have ran simulated tests on 3 connections @ ~1 million records each and no collisions.

CREATE TABLE `aut` (
  `au_id` int(10) NOT NULL AUTO_INCREMENT,
  `au_control` int(10) DEFAULT NULL,
  `au_name` varchar(50) DEFAULT NULL,
  `did` int(10) DEFAULT NULL,
  PRIMARY KEY (`au_id`),
  KEY `Did` (`did`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

TRIGGER `binc_control` BEFORE INSERT ON `aut` 
FOR EACH ROW BEGIN
SET NEW.AU_CONTROL = (SELECT COUNT(*)+1 FROM aut WHERE did = NEW.did);
END;