I also had a thought this morning - pre coffee, even!
make a table called "ids"
create table ids
(tablename varchar(32) primary key,
currentId int);
seed it:
insert into ids ("person",1);
when you need an ID, get it OUTSIDE of the transaction, eg via an SP, but
the kicker is:
--first get the original value
select currentid into $currentid from ids where tablename = "person"
--up it
$newcurrentid = $currentId + 1;
--set it back without dirty-writeing it
update ids set currentid = $newcurrentid where currentid = $currentid and
tableName = "person"
if this returns that it updated 0 rows, TRY AGAIN, as someone else has
beaten you to it. if you get a 1 row updated message, your golden. you
should have autocommit on for this tho.
you can't roll them back if you need to roll back tho - its not really
possible, I dont think, without updating all the other records that used the
keys after it.
Nic.
----- Original Message -----
From: "Neven MacEwan" <[EMAIL PROTECTED]>
To: "Multiple recipients of list delphi" <[EMAIL PROTECTED]>
Sent: Friday, January 12, 2001 9:53 AM
Subject: Re: [DUG]: [DUG-DB]: Unique IDs
> Stephen
>
> think about it last night why do you
>
> 1/ Code the 'generator' using a global table with an identity column
> ie
>
> create proc GetID as
> begin
> declare @NewID int
> insert into GenTable (InUse) values ('Y')
> set @NewID = @@identitiy
> return @NewID
> end
>
> then call this outside your transaction
>
> -- get id's
> exec @NewID1 = GetID
> begin tran
> commit
>
> occasionally you could clear the GenTable or use is as a log
> or you could supplement your GetID proc with a reuse code to reuse any
> ID's not used (not that i'd suggest this)
>
> create proc FreeID @ID integer as
> begin
> update GenTable set InUse = 'N' where GenTableID = @ID
> end
>
> and alter GetID to check for possible Free ID's
>
> HTH
> Neven
>
> --------------------------------------------------------------------------
-
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
> To UnSub, send email to: [EMAIL PROTECTED]
> with body of "unsubscribe delphi"
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
