If you're using a database which supports an identity column you can use a
scheme like the following. We have done this so that key references are
unique in the database, not just the table. Note that the "entity" column
is required in some databases as you cannot insert a null row. It's
certainly not the most efficient method but it is bulletproof and works well
in most databases I'm using.
CREATE TABLE RefGenerator (
NextRef int IDENTITY(1, 1) NOT NULL ,
entity varchar(20) NULL
)
CREATE PROCEDURE GetNextRef
@ref int OUTPUT
AS
INSERT INTO RefGenerator (entity) values ('xx')
SET @ref = @@identity
RETURN @@identity -- Only used for quick access without parameters.
-- Normally the return value is used for error
checking.
CREATE TRIGGER i_RefGenerator ON RefGenerator FOR INSERT
AS
DELETE FROM RefGenerator WHERE NextRef < @@identity
If the database supports functions a function wrapping the procedure can be
called directly in any insert statement.
HTH
Stephen
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
