CODEWITHSUNDEEP
javamysqltriggersinsertmysql-workbench
Kyte
Kyte

Reputation: 834

Creating a mysql trigger for use with batch insert statements

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!

Upvotes: 0

Views: 1133

Answers (1)

juergen d
juergen d

Reputation: 204766

No, no, no no!

Don't misuse the auto-increment primay key in such way! It is meant to be auto-generated and untouched. Just leave it.

If you need an indicator in which order your rows where inserted then use another column. For instance a datetime column with a default value like current_timestamp. That way it will be filled automatically too and you don't have to do anything.

Upvotes: 1

Related Questions