How to Atomically Insert or Update a Row in T-SQL Using @@rowcount?

Atomic Row Insertion with Existence Check in T-SQL

Your requirement is to develop a T-SQL stored procedure that atomically updates a row in a table. In case the row does not exist, it should be inserted within a transaction, ensuring reliability and atomicity. You need to utilize @@rowcount effectively to determine whether the row already exists.

Suggested Implementation

Here's a modified version of your code that incorporates the suggested approach:

BEGIN TRANSACTION;

IF EXISTS (SELECT 1 FROM Bookings WHERE FlightId = @Id)
BEGIN
    UPDATE Bookings
    SET TicketsBooked = TicketsBooked + @TicketsToBook
    WHERE FlightId = @Id AND TicketsMax >= (TicketsBooked + @TicketsToBook);
END
ELSE
BEGIN
    INSERT INTO Bookings ... (omitted);
END

/* Remaining transaction logic */

COMMIT TRANSACTION;

IF @@ERROR = 0
BEGIN
    RETURN TRUE;  -- Return success if no errors occurred
END
ELSE
BEGIN
    RETURN FALSE; -- Return failure in case of errors
END

Explanation:

• We use IF EXISTS to check if a row with the specified FlightId already exists.
• If it exists, we perform the update operation. Otherwise, we proceed with the insert operation.
• We include a condition in the update statement to ensure that the new ticket count does not exceed the maximum allowed.
• We use @@ERROR to check for any errors during the transaction and return TRUE or FALSE accordingly.

The above is the detailed content of How to Atomically Insert or Update a Row in T-SQL Using @@rowcount?. For more information, please follow other related articles on the PHP Chinese website!