Clé composite unique SQL Server de deux champs avec deuxième auto-incrémentation de champ

Depuis que quelqu'un a posé une question similaire, j'ai réfléchi à cela. Le premier problème est que les DB ne fournissent pas de séquences "partitionnables" (qui redémarreraient / se souviendraient en fonction de différentes keys). La seconde est que les objects SEQUENCE fournis sont orientés autour d'un access rapide et ne peuvent pas être annulés (c'est-à-dire que vous aurez des trous). Ceci exclut essentiellement l'utilisation d'un utilitaire embedded … ce qui veut dire que nous devons nous-mêmes rouler.

La première chose dont nous aurons besoin est une table pour stocker nos numéros de séquence. Cela peut être assez simple:

 CREATE TABLE Invoice_Sequence (base CHAR(1) PRIMARY KEY CLUSTERED, invoiceNumber INTEGER); 

En réalité, la colonne de base doit correspondre à une reference de key étrangère, quelle que soit la table / l'ID définie (s) par les entresockets / entités pour lesquelles vous émettez des factures. Dans cette table, vous voulez que les inputs soient uniques par entité émise.

Ensuite, vous voulez un proc stocké qui prendra une key ( base ) et cracher le numéro suivant dans la séquence ( invoiceNumber ). L'set des keys nécessaires variera (c.-à-d. Que certains numéros de facture doivent contenir l'année ou la date complète d'émission), mais le formulaire de base pour cette situation est le suivant:

 CREATE PROCEDURE Next_Invoice_Number @baseKey CHAR(1), @invoiceNumber INTEGER OUTPUT AS MERGE INTO Invoice_Sequence Stored USING (VALUES (@baseKey)) Incoming(base) ON Incoming.base = Stored.base WHEN MATCHED THEN UPDATE SET Stored.invoiceNumber = Stored.invoiceNumber + 1 WHEN NOT MATCHED BY TARGET THEN INSERT (base) VALUES(@baseKey) OUTPUT INSERTED.invoiceNumber ;; 

Notez que:

1. Vous devez exécuter ceci dans une transaction sérialisée
2. La transaction doit être la même que celle qui est insérée dans la table de destination (facture).

C'est vrai, vous aurez toujours bloquer par entreprise lors de l'émission des numéros de facture. Vous ne pouvez pas éviter cela si les numéros de facture doivent être séquentiels, sans aucun écart – jusqu'à ce que la ligne soit réellement validée, elle pourrait être annulée, ce qui signifie que le numéro de facture n'aurait pas été émis.

Maintenant, puisque vous ne voulez pas avoir à callbacker d'appeler la procédure pour l'input, enveloppez-le dans un triggersur:

 CREATE TRIGGER Populate_Invoice_Number ON Invoice INSTEAD OF INSERT AS DECLARE @invoiceNumber INTEGER BEGIN EXEC Next_Invoice_Number Inserted.base, @invoiceNumber OUTPUT INSERT INTO Invoice (base, invoiceNumber) VALUES (Inserted.base, @invoiceNumber) END 

(évidemment, vous avez plus de colonnes, y compris d'autres qui devraient être remplies automatiquement – vous devrez les remplir)
… que vous pouvez ensuite utiliser en disant simplement:

 INSERT INTO Invoice (base) VALUES('A'); 

Alors, qu'avons-nous fait? La plupart du time, tout ce travail consistait à réduire le nombre de lignes bloquées par une transaction. Jusqu'à ce que cet INSERT soit validé, il n'y a que deux lignes verrouillées:

• La ligne dans Invoice_Sequence maintenant le numéro de séquence
• La ligne dans Invoice pour la nouvelle facture.

Toutes les autres lignes pour une base particulière sont libres – elles peuvent être mises à jour ou interrogées à volonté (la suppression d'informations provenant de ce type de système tend à rendre les comptables nerveux). Vous devez probablement décider de ce qui doit se produire lorsque les requêtes incluent normalement la facture en attente …

vous pouvez utiliser le triggersur pour avant insertion et affecter la valeur suivante en prenant le max (id) avec le filter "base" qui est "A" dans ce cas.
Cela vous donnera la valeur max (id) comme 2 et que l'incrémenter de max (id) +1. Poussez maintenant la nouvelle valeur dans le champ "id". avant l'insertion.
Je pense que cela peut vous aider

Déclencheurs MSSQL: http://msdn.microsoft.com/fr-fr/library/ms189799.aspx

Table de test

 CREATE TABLE MyTable ( base CHAR(1), id INT ) GO 

Définition du triggersur

 CREATE TRIGGER dbo.tr_Populate_ID ON dbo.MyTable INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO MyTable (base,id) SELECT i.base, ISNULL(MAX(mt.id),0) +1 AS NextValue FROM inserted i left join MyTable mt on i.base = mt.base GROUP BY i.base END 

Tester

Exécutez l'instruction suivante plusieurs fois et vous verrez les prochaines valeurs disponibles dans ce groupe seront affectées à l'ID.

 INSERT INTO MyTable VALUES ('A'), ('B'), ('C') GO SELECT * FROM MyTable GO