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.

Member Avatar
1stDAN

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;).

Or in the insert you can do

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

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.

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.