Re: Incrementing sequence number

From: mars1972@my-deja.com > >I would probably put a unique index on (name, sequence). That way if >you do happen to get a dup, the second will error and you can find the >max again. > >I almost said something about using a trigger, but I just remembered >that I think there's some constriction on using triggers to modify >records in the table that the trigger is on. Something about a trigger >executing a statement that would cause the trigger to execute again, >causing an infinite loop. > > > OK, you've all come across this scenario I'm sure, so can someone save > > me having to reinvent this particular wheel, please? > > > > You've got this table which stores, among other things, a name and a > > sequence number within name: > > > > Annie 1 > > Annie 2 > > Annie 3 > > Bob 1 > > Bob 2 > > Carol 1 > > Carol 2 > > Carol 3 > > etc > > > > Now I need to insert a new row for 'Annie'. I need to select the > > maximum sequence number currently in use for this name, increment it, > > and insert my new row with the newly incremented sequence number. > > > > How the devil do I do this from ESQLC in an atomic fashion, ie. how do > > I ensure that no-one reads the max sequence after I have read it but > > before I have inserted my new row? > > > > Sent via Deja.com http://www.deja.com/ > > Before you buy. > > > >-- ># unrm / >ksh: unrm: not found ># man cpio > > >Sent via Deja.com http://www.deja.com/ >Before you buy. I'd just like to add that ESQL/C probably isn't the best choice for a development platform where you don't want to reinvent the wheel... :0) ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com