AutoNumber Primary Key

We are testing the upload of an Access 2002 data database to SQL Server
2000. Some of the tables had AutoNumber fields that were Random (due to it
being a replicated table in Access). The upload created triggers similar to
the example below. However, Access applications fail on insert because the
primary key field is not populated until the trigger runs. Any help is
appreciated.
CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR INSERT AS
SET NOCOUNT ON
DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
/* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
SELECT @.newc = (SELECT DocID FROM inserted)
UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
David
David C wrote:
> We are testing the upload of an Access 2002 data database to SQL
> Server 2000. Some of the tables had AutoNumber fields that were
> Random (due to it being a replicated table in Access). The upload
> created triggers similar to the example below. However, Access
> applications fail on insert because the primary key field is not
> populated until the trigger runs. Any help is appreciated.
> CREATE TRIGGER T_AssessmentDocs_ITrig ON dbo.AssessmentDocs FOR
> INSERT AS SET NOCOUNT ON
> DECLARE @.randc int, @.newc int /* FOR AUTONUMBER-EMULATION CODE */
> /* * RANDOM AUTONUMBER EMULATION CODE FOR FIELD 'DocID' */
> SELECT @.randc = (SELECT convert(int, rand() * power(2, 30)))
> SELECT @.newc = (SELECT DocID FROM inserted)
> UPDATE AssessmentDocs SET DocID = @.randc WHERE DocID = @.newc
> David
Change the column to an IDENTITY value. SQL Server automates the
generation of the next value. You may have to seed the identity value
using the MAX(ID) + 1 in the table.
You can pull back the newly inserted identity value into the application
using the SCOPE_IDENTITY() function.
David Gugick
Imceda Software
www.imceda.com
|||David Gugick wrote:
> David C wrote:
> Change the column to an IDENTITY value. SQL Server automates the
> generation of the next value. You may have to seed the identity value
> using the MAX(ID) + 1 in the table.
Or by using DBCC CHECKIDENT, which should do the same job.