Are Triggers Based On Queries Atomic?
- by David
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;
