Stephen
2 questions
1/ Why dont you want to use Identity cols?
2/ If all you are doing is inserting why use a transaction? (that shoud
start some comment)
and
> Perhaps I should just go back to a fully normalised database
Why did you ever leave? fully! I think not! maybe third normal...
Neven
----- Original Message -----
From: Stephen Bertram <[EMAIL PROTECTED]>
To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
Sent: Friday, 12 January 2001 11:27
Subject: RE: [DUG]: [DUG-DB]: Unique IDs
> 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"
>
---------------------------------------------------------------------------
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"
