ALTER DATABASE MyDatabase SET ALLOW_SNAPSHOT_ISOLATION ON;

IF object_id('dbo.TEST_InventoryActivity') IS NOT NULL
    DROP TABLE dbo.TEST_InventoryActivity
GO

CREATE TABLE dbo.TEST_InventoryActivity
(   activityID int not null primary key identity
    , itemID int not null
    , inOrOut char(1) not null
    , quantity int not null
    , modBy varchar(128) not null
    , modDate datetime not null default getdate()
)
go

INSERT INTO TEST_InventoryActivity(itemID, inOrOut, quantity, modBy)
VALUES (1,'I',100, 'setups')
;
--show all records
SELECT i.*
FROM TEST_InventoryActivity i
;
--show inventory balances
SELECT i.itemID, inventoryBalance = sum(i.quantity)
FROM TEST_InventoryActivity i
GROUP BY i.itemID
;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRAN;

DECLARE @itemID int, @quantity int;
set @itemID = 1;
set @quantity = -75;

insert into TEST_InventoryActivity(itemID, inOrOut, quantity, modBy)
values(@itemID,'O', @quantity, 'test 1') --use Item 1
;

IF EXISTS(
    SELECT i.itemID, sum(i.quantity)
    FROM TEST_InventoryActivity i
    WHERE i.itemID = @itemID
    GROUP BY i.itemID
    HAVING sum(i.quantity) < 0
)
BEGIN
    ROLLBACK;
    RAISERROR(N'Not enough remaining inventory', 16, 1);
    RETURN;
END
;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

BEGIN TRAN;

DECLARE @itemID int, @quantity int;
set @itemID = 1;
set @quantity = -50;

insert into TEST_InventoryActivity(itemID, inOrOut, quantity, modBy)
values(@itemID,'O', @quantity, 'test 2') --use Item 1
;

IF EXISTS(
    SELECT i.itemID, sum(i.quantity)
    FROM TEST_InventoryActivity i
    WHERE i.itemID = @itemID
    GROUP BY i.itemID
    HAVING sum(i.quantity) < 0
)
BEGIN
    ROLLBACK;
    RAISERROR(N'Not enough remaining inventory', 16, 1);
    RETURN;
END
;

COMMIT;

COMMIT;

--show all records
SELECT i.*
FROM TEST_InventoryActivity i
;
--show inventory balances
SELECT i.itemID, inventoryBalance = sum(i.quantity)
FROM TEST_InventoryActivity i
GROUP BY i.itemID

IF      EXISTS(SELECT   i.itemID,
                        sum(i.quantity)
                FROM    TEST_InventoryActivity i With (READCOMMITTED)
                WHERE   i.itemID = @itemID
                GROUP   BY i.itemID
                HAVING  sum(i.quantity) < 0)
BEGIN
        ROLLBACK;
        RAISERROR(N'Not enough remaining inventory', 16, 1);
        RETURN;
END

SELECT  CASE transaction_isolation_level
        WHEN 0 THEN 'Unspecified'
        WHEN 1 THEN 'ReadUncommitted'
        WHEN 2 THEN 'ReadCommitted'
        WHEN 3 THEN 'Repeatable'
        WHEN 4 THEN 'Serializable'
        WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL
FROM    sys.dm_exec_sessions
WHERE   session_id = @@SPID