I'm working with MySQL Workbench 5.2 CE and I'm having trouble building a trigger. I have a table set up like so, with the 'id' field being an auto increment
TABLE
addr VARCHAR
id INT
and a batch statement written in Java
String state1 = "insert ignore into TABLE(addr) values (?);"
and a trigger set up like so
USE DB_NAME;
DELIMITER $$
CREATE TRIGGER `check_addr` BEFORE INSERT ON tableFOR EACH ROW
-- Edit trigger body code below this line. Do not edit lines above this one
IF ( SELECT * FROM table WHERE addr = NEW.addr ) = 0
THEN insert into table(addr) values (NEW.addr);
END IF;
All of which is me trying to avoid gaps in my auto-increment values. I'm using the table to store a lot of network-related information, so the potential gaps in the auto-increment field would get pretty massive.
The goal with the trigger is to avoid the insert statement (and subsequent increment of the auto increment field) and silently abort. This way the auto-increment values stay consecutive.
A quick note: the addr field is a PK.
Thanks.
EDIT 12/4/2013 Just as an update, I dropped the AI field and opted instead for a composite key. Uses slightly more disk space, but I got clever with normalizing other fields so it worked out in the end. Thanks for the help!
