Member Avatar for strongpot

I am completely new to Triggers but I hear they can help me with a specific problem.

I have a table:

picid        picture        recordListingID
1            danny.jpg      0
2            jane.jpg       0
3            ralph.jpg      0

In the recordListingID column (int) i would like to generate numbers sequentially upon an INSERT. Is it possible? Can it be done without a trigger? Thanks in advance.

  • 4 Contributors
  • 4 Replies
  • 279 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by strongpot

Dani AI

Generated

A few practical options and important caveats that build on the replies already here (notes for , , and ):

If you need a single global, strictly increasing value but you can’t create triggers on the host, use a tiny “counter” table and grab the next value atomically. This requires only normal DML (no TRIGGER or SUPER). Example pattern (create the counter once, then run the three statements on the same connection for each insert):

CREATE TABLE seq_counters (name VARCHAR(64) PRIMARY KEY, val BIGINT NOT NULL);
INSERT INTO seq_counters (name,val) VALUES ('recordListingID', 3);

-- when inserting a new picture:
UPDATE seq_counters
  SET val = LAST_INSERT_ID(val + 1)
  WHERE name = 'recordListingID';
SET @next = LAST_INSERT_ID();
INSERT INTO pictures (picture, recordListingID) VALUES ('new.jpg', @next);

For per-group numbering (separate sequence per listing/user), use a keyed counter row per group (e.g. name = 'listing_42') and the same UPDATE/LAST_INSERT_ID trick, or use InnoDB transactions with SELECT ... FOR UPDATE to increment safely inside a transaction.

Warnings and tips:

  • Do not rely on SELECT MAX(...) + 1 without locking — it races under concurrent inserts and will produce duplicates.
  • MySQL allows only one AUTO_INCREMENT column per table; if you already use AUTO_INCREMENT for picid you can’t add a second AUTO_INCREMENT column.
  • CREATE TRIGGER requires the TRIGGER privilege and some shared hosts disable it; if hosting blocks triggers, the counter-table approach or application-level transactions are the safest alternatives.

If the goal is just "unique and increasing" (not gap-free), AUTO_INCREMENT is simplest when available. If you need gap-free, use transactional counter logic and ensure the storage engine and isolation level you use support the locking approach you pick.

Recommended Answers

All 4 Replies

Member Avatar for Member #789783

if you create your table, you can specify column recordListingID like recordListingID int NOT NULL auto_increment. So every time you insert a new row recordListingID will be automatically incremented by 1 starting with 1.

You can also do this by a trigger: define a before-insert trigger. Within this trigger search for maximum recordListingID value of already stored rows. Insert new row with recordListingID value maximum+1 (within trigger: set new.recordListingID = maximum + 1;).

Edited by Member #789783
Member Avatar for Reverend Jim

Or in the insert you can do

INSERT INTO mytable (picid,picture,recordListingID)
VALUES((SELECT MAX(picid)+1 FROM mytable),'george.jpg',0)
Member Avatar for IIM

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows:

So create a table using query,

CREATE TABLE mytable (
     picid INT NOT NULL AUTO_INCREMENT,
     name CHAR(30) NOT NULL,
     PRIMARY KEY (id)
) ENGINE=MyISAM;

and then while inserting just add values to other columns,this value will be automatically filled by mysql server,mysql service maintains a track of the last id used ,so it is much quicker that using subquery.

INSERT into mytable(name) values ('sad');

So in this case even if you delete a row from a table,the next value will be 1 greater than the deleted value while using max function and incrementing 1 will make sure that you will always get one greater than the max id found in table.

Member Avatar for strongpot

Thanks, it seems like this is working except for Godaddy not allowing Super Priv on their shared hosting. I hate godaddy so much. My boss loves them because they do superbowl commercials. FML. Thanks though.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.