Neven
You are right in the "normal" cases. What I'm thinking of (don't read this
if you have a bad heart!) is the case where a procedure to insert a row in
table_1 triggers multiple row insertions in table_2.
Something like :
Get ID
Start Transaction
Insert row in table_1
Insert trigger : until all cascade inserts are complete
begin
Get ID
Insert row in subsidiary table !! failure here is the problem
end
Commit transaction
Since the number of cascaded inserts is unknown you can't book a block of
IDs - or can you? - and it seems to me that the Get ID call within the
transaction is unavoidable.
This is heading fot the too hard basket - at first look it appeared to solve
5 problems I'm facing where I have generic tables interfacing to multiple
master tables. Perhaps I should just go back to a fully normalised database
<g>.
One of the design issues I am looking at is a post-it note table that allows
the user to add a notation to any data item within the application. If IDs
are unique within the database every SQL statement can have an outer join to
the post-it table without having to identify the master table. If I add the
master table ID it solves the problem, but adds to the SQL maintenance. I
was looking for a quick way out :(
I will continue to mull this over.
Thanks for all the input
Stephen
-----Original Message-----
From: Neven MacEwan [mailto:[EMAIL PROTECTED]]
Sent: Friday, 12 January 2001 9:54 a.m.
To: Multiple recipients of list delphi
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"
