Primary key / tallrekker på MSSQL

[mysjkin]

Jeg er i ferd med å utvide en LIMS-database (Laboratory Information Management System, en database som holder rede på det meste man trenger å holde rede på på en kjemisk lab). En viktig ting her er selvfølgelig prøver, som skal nummereres unikt i LIMSen. Noe av utvidelsen er at vi har 3 labber som skal bruke samme LIMS, og de ønsker alle å ha en separat løpende nummerering av sine prøver, med andre ord hvis vi kaller labbene Ø,T og S, så vil jeg, eeh de, ha nummerering Ø1,Ø2,Ø3 osv på Ø, T1,T2 osv på T og S1, S2 osv på S. (evt padding med 0 er kommer i tillegg) En løsning hadde selvfølgelig vært å bruke tre separate databaser, men IT-avdelingen har satt foten ned der. Det beste er vel å bruke en eller annen form for generator / trigger i serveren, jeg har funnet noe stoff som omhandler dette problemet, men hadde vært glad om noen har noe mer referanser / ideer / tanker omkring dette (å lage tre parallelle serier med løpenummere). Også greit om det ike er for vanskelig å endre antallet om det er aktuelt.

 

Ja, riktig, serveren er MSSQL og frontenden er skrevet i delphi.

 

M.

[j000rn]

Noe sånt kanskje?

 

INSERT INTO Tests(Dept,TestNumber,TestName)

SELECT 'Ø',MAX(TestNumber)+1,'Testing av blah' FROM Tests WHERE Dept = 'Ø';

 

Hvis du setter primarykey på både Dept og TestNumber så sikrer du at disse er unike også.

 

Dette krever at det allerede ligger minst 1 rad for hver 'dept' inne, hvis ikke feiler MAX saken.

 

 

Det beste er nok å lage en stored procedure for å insert'e data.

[kaffenils]

Den riktige måten å gjøre det på er som følger:

 

Opprett en tabell med liste over alle lab'ene. Kall tabellen 'Lab'. Denne inneholder en LabID kolonne av datatype int og blir brukt som fremmednøkkel i teller- og resultattabellen senere.

 

Opprett en tabell som holder kontroll på neste ledige nummer for hver lab. I eksempelet har jeg kalt denne tabellen 'Lab_Resultat_Teller'. Tabellen inneholder 'LabID' som er primærnøkkel og fremmednøkkel til Lab tabellen. I tillegg inneholder tabellen kolonnene 'NesteNummer' (int) og 'Prefix' (varchar(3)).

 

Den siste tabellen er den som inneholder resultatene. Kolonnene her er 'ResultatId' (varchar(10)), 'LabID' (int) og 'Tittel' (varchar(50)).

 

Dette scriptet oppretter tabellene over hvis du ikke gidder å gjøre det selv.

CREATE TABLE [dbo].[Lab] (
[LabID] [int] IDENTITY (1, 1) NOT NULL ,
[Navn] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Lab_Resultat_Teller] (
[LabID] [int] NOT NULL ,
[NesteNummer] [int] NOT NULL ,
[Prefix] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Lab_Resultater] (
[ResultatID] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LabID] [int] NOT NULL ,
[Tittel] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Lab] WITH NOCHECK ADD 
CONSTRAINT [PK_Lab] PRIMARY KEY  CLUSTERED 
(
 [LabID]
)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Lab_Resultat_Teller] WITH NOCHECK ADD 
CONSTRAINT [PK_Lab_Resultat_Teller] PRIMARY KEY  CLUSTERED 
(
 [LabID]
)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Lab_Resultater] WITH NOCHECK ADD 
CONSTRAINT [PK_LabResultater] PRIMARY KEY  CLUSTERED 
(
 [ResultatID]
)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Lab] ADD 
CONSTRAINT [IX_Lab] UNIQUE  NONCLUSTERED 
(
 [Navn]
)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Lab_Resultat_Teller] ADD 
CONSTRAINT [DF_Lab_Resultat_Teller_NesteNummer] DEFAULT (1) FOR [NesteNummer],
CONSTRAINT [IX_Lab_Resultat_Teller] UNIQUE  NONCLUSTERED 
(
 [Prefix]
)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Lab_Resultat_Teller] ADD 
CONSTRAINT [FK_Lab_Resultat_Teller_Lab] FOREIGN KEY 
(
 [LabID]
) REFERENCES [dbo].[Lab] (
 [LabID]
)
GO

ALTER TABLE [dbo].[Lab_Resultater] ADD 
CONSTRAINT [FK_Lab_Resultater_Lab] FOREIGN KEY 
(
 [LabID]
) REFERENCES [dbo].[Lab] (
 [LabID]
)
GO

 

Vi trenger også er stored procedure for å registrer nye labresultater. Prosedyren tar i mot LabID og Tittel som parametre og returnerer den nye recorden som et recordset. Det som er viktig under registreringen er å sørge for at prosedyren med 100% sikkerhet, ikke prøver å legge inn en resultat-record med samme nummeret flere ganger hvis flere brukere prøver å registrere et resultat "nøyaktig" samtidig.

 

For å forsikre seg mot at dette skjer gjør prosedyren følgende:

1. Begynner en ny transaksjon.

2. Denne er viktig: Før prosedyren leser neste ledige sekvens så inkrementerer den 'NesteNummer'. Dette forhindrer både at andre får lest 'NesteNummer' etter at du har begynt din transaksjon og det forhindrer derfor også at du får lest 'NesteNummer' etter at noen andre har begynt en transaksjon.

3. Etter at 'NesteNummer' er oppdatert, leses denne 'NesteNummer' minus 1 og legges i variabelen @NesteNummer. @Prefix og @Nestenummer danner tilsammen verdien som legges inn i @ResultatId (resultat-registrerings nummeret mao.).

4. Resultat-recorden registreres og returneres til brukeren som et recordset (hvis du bruker ADO).

5. Transaksjonen committes (noen god norsk oversetting?). Først nå kan andre registrere sine labresultater.

 

NB! Jeg har, som du ser, ikke lagt inn noen form for feilhåndering i prosedyren. Feil kan f.eks. oppstå ved timeout, hvis @ResultatId finnes fra før (ja, det kan skje hvis noen legger inn et resultat manuelt, eller du endrer Prefix eller NesteNummer som igjen fører til at eksisterende nummer blir generert).

 

Her er prosedyren som oppretter nytt labresultat:

CREATE PROCEDURE RegistrerNyttResultat
@LabID int, @Tittel as varchar(50)
AS

declare @NesteNummer int, @Prefix varchar(3), @ResultatID varchar(10)

begin tran

DECLARE Teller CURSOR DYNAMIC FOR
SELECT Prefix FROM Lab_Resultat_Teller where LabID=@LabID

OPEN Teller

FETCH NEXT FROM Teller 
INTO @Prefix

IF @@FETCH_STATUS = 0
BEGIN

update Lab_Resultat_Teller set NesteNummer=(NesteNummer+1) where current of Teller

set @NesteNummer=(select (NesteNummer-1) from Lab_Resultat_Teller where LabID=@LabID)

set @ResultatID=@Prefix+cast(@NesteNummer as varchar(7))

insert into Lab_Resultater(ResultatID, LabID, Tittel) values(@ResultatID,@LabId,@Tittel)

select * from Lab_Resultater where ResultatID=@ResultatID

select cast(1 as varchar(20))

END

close Teller

deallocate Teller

commit tran
GO

[mysjkin]

Wow, dette var mer enn jeg regnet med. Jeg var inne på den retningen Kaffenils skisserte, men jeg hadde ikke på langt nær kommet dit.

 

Takker og bukker!

 

M.