Vérifier si une ligne existe, sinon insérer

Question

Je dois écrire une procédure stockée T-SQL qui met à jour une ligne dans une table. Si la ligne n'existe pas, l'insérer. Toutes ces étapes sont enveloppées par une transaction.

Il s'agit d'un système de réservation, il doit donc être atomique et fiable . Il doit retourner vrai si la transaction a été validée et le vol réservé.

Je suis débutant en T-SQL et je ne sais pas comment utiliser @@rowcount . Voilà ce que j'ai écrit jusqu'à présent. Suis-je sur la bonne voie ? Je suis sûr que c'est un problème facile pour vous. Merci

-- BEGIN TRANSACTION (HOW TO DO?)

UPDATE Bookings
 SET TicketsBooked = TicketsBooked + @TicketsToBook
 WHERE FlightId = @Id AND TicketsMax < (TicketsBooked + @TicketsToBook)

-- Here I need to insert only if the row doesn't exists.
-- If the row exists but the condition TicketsMax is violated, I must not insert 
-- the row and return FALSE

IF @@ROWCOUNT = 0 
BEGIN

 INSERT INTO Bookings ... (omitted)

END

-- END TRANSACTION (HOW TO DO?)

-- Return TRUE (How to do?)

Answer 1

Je suppose qu'une seule ligne pour chaque vol ? Si c'est le cas :

IF EXISTS (SELECT * FROM Bookings WHERE FLightID = @Id)
BEGIN
    --UPDATE HERE
END
ELSE
BEGIN
   -- INSERT HERE
END

J'assume ce que j'ai dit, car votre façon de faire peut surbooker un vol, puisqu'elle insère une nouvelle ligne alors qu'il y a 10 billets maximum et que vous en réservez 20.

Answer 2

Jetez un coup d'œil à MERGE commande Vous pouvez effectuer une mise à jour, une insertion et une suppression dans une seule déclaration.

Voici une mise en œuvre de l'utilisation de MERGE
- Il vérifie si le vol est plein avant de faire une mise à jour, sinon il fait une insertion.

if exists(select 1 from INFORMATION_SCHEMA.TABLES T 
              where T.TABLE_NAME = 'Bookings') 
begin
    drop table Bookings
end
GO

create table Bookings(
  FlightID    int identity(1, 1) primary key,
  TicketsMax    int not null,
  TicketsBooked int not null
)
GO

insert  Bookings(TicketsMax, TicketsBooked) select 1, 0
insert  Bookings(TicketsMax, TicketsBooked) select 2, 2
insert  Bookings(TicketsMax, TicketsBooked) select 3, 1
GO

select * from Bookings

Et puis...

declare @FlightID int = 1
declare @TicketsToBook int = 2

--; This should add a new record
merge Bookings as T
using (select @FlightID as FlightID, @TicketsToBook as TicketsToBook) as S
    on  T.FlightID = S.FlightID
      and T.TicketsMax > (T.TicketsBooked + S.TicketsToBook)
  when matched then
    update set T.TicketsBooked = T.TicketsBooked + S.TicketsToBook
  when not matched then
    insert (TicketsMax, TicketsBooked) 
    values(S.TicketsToBook, S.TicketsToBook);

select * from Bookings

Answer 3

Passez les indices updlock, rowlock, holdlock lors du test d'existence de la ligne.

begin tran /* default read committed isolation level is fine */

if not exists (select * from Table with (updlock, rowlock, holdlock) where ...)
    /* insert */
else
    /* update */

commit /* locks are released here */

L'indication updlock force la requête à prendre un verrou de mise à jour sur la rangée si elle existe déjà, empêchant les autres transactions de la modifier jusqu'à ce que vous commitiez ou fassiez un retour en arrière.

L'indication holdlock force la requête à prendre un verrou de plage, empêchant d'autres transactions d'ajouter une ligne correspondant à vos critères de filtrage jusqu'à ce que vous validiez ou annuliez.

L'indice rowlock force la granularité du verrouillage au niveau de la ligne au lieu du niveau de la page par défaut, de sorte que votre transaction ne bloquera pas d'autres transactions essayant de mettre à jour des lignes non liées dans la même page (mais soyez conscient du compromis entre la réduction de la contention et l'augmentation de la surcharge de verrouillage - vous devriez éviter de prendre un grand nombre de verrous de niveau ligne dans une seule transaction).

Voir http://msdn.microsoft.com/en-us/library/ms187373.aspx pour plus d'informations.

Notez que les verrous sont pris au fur et à mesure de l'exécution des instructions qui les prennent - invoquer begin tran ne vous donne pas d'immunité contre une autre transaction qui prend les verrous sur quelque chose avant que vous ne l'atteigniez. Vous devriez essayer de factoriser votre SQL afin de conserver les verrous le moins longtemps possible en validant la transaction dès que possible (acquisition tardive, libération précoce).

Notez que les verrous de niveau ligne peuvent être moins efficaces si votre PK est un bigint, car le hachage interne de SQL Server est dégénéré pour les valeurs 64 bits (différentes valeurs de clé peuvent être hachées vers le même id de verrou).

Answer 4

Je suis en train d'écrire ma solution. Ma méthode n'a pas besoin de 'if' ou de 'merge'. Elle est simple.

INSERT INTO TableName (col1,col2)
SELECT @par1, @par2
   WHERE NOT EXISTS (SELECT col1,col2 FROM TableName
                     WHERE col1=@par1 AND col2=@par2)

Par exemple :

INSERT INTO Members (username)
SELECT 'Cem'
   WHERE NOT EXISTS (SELECT username FROM Members
                     WHERE username='Cem')

Explication :

(1) SELECT col1,col2 FROM TableName WHERE col1=@par1 AND col2=@par2 Cette méthode sélectionne les valeurs recherchées dans TableName.

(2) SELECT @par1, @par2 WHERE NOT EXISTS Il prend if not exists de la sous-requête (1)

(3) Insertion dans les valeurs de l'étape TableName (2)

Answer 5

C'est quelque chose que j'ai dû faire récemment :

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cjso_UpdateCustomerLogin]
    (
      @CustomerID AS INT,
      @UserName AS VARCHAR(25),
      @Password AS BINARY(16)
    )
AS 
    BEGIN
        IF ISNULL((SELECT CustomerID FROM tblOnline_CustomerAccount WHERE CustomerID = @CustomerID), 0) = 0
        BEGIN
            INSERT INTO [tblOnline_CustomerAccount] (
                [CustomerID],
                [UserName],
                [Password],
                [LastLogin]
            ) VALUES ( 
                /* CustomerID - int */ @CustomerID,
                /* UserName - varchar(25) */ @UserName,
                /* Password - binary(16) */ @Password,
                /* LastLogin - datetime */ NULL ) 
        END
        ELSE
        BEGIN
            UPDATE  [tblOnline_CustomerAccount]
            SET     UserName = @UserName,
                    Password = @Password
            WHERE   CustomerID = @CustomerID    
        END

    END