Guarantee reliability

[stockton]

I have a stored procedure that I wish to use to update two tables reliably.

In other words either both tables are updated or neither.

How do I achieve this?

CREATE PROCEDURE spIssueScannedTickets
    @iEventID int,
    @MemberNum nvarchar(12),
    @BatchSize int,
    @FirstNumber nvarchar(12),
    @LastNumber nvarchar(12),
    @SlotsBalance int,
    @TableBalance int,
    @BonusBalance int,
    @UserID    int
AS
DECLARE @Result int
SET @Result = 0
BEGIN TRANSACTION
INSERT INTO BundlesIssued (MemberNumber, EventID, BundleSize, FirstNumber, LastNumber, DateIssued, UserID, Invalid)
    VALUES (@MemberNum, @iEventID, @BatchSize, @FirstNumber, @LastNumber, GETDATE(), @UserID , 0x00)
if  (@SlotsBalance>0)
INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
     VALUES (GETDATE(),@iEventID,@MemberNum, 'Slots', @SlotsBalance, 0, @UserID)
if  (@TableBalance>0)
INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
     VALUES (GETDATE(),@iEventID,@MemberNum, 'Tables', @TableBalance, 0, @UserID)
if  (@BonusBalance>0)
INSERT INTO TicketsIssued (Date, EventID, MemberNumber, EarnType, NumTicketsIssued, Turnover, UserID)
     VALUES (GETDATE(),@iEventID,@MemberNum, 'Bonus', @BonusBalance, 0, @UserID)

COMMIT TRANSACTION
SET @Result = @BatchSize
RETURN @Result

GO

???