----------------------------------------------------------------
--                                                              
--  File:   Atomicity_RB.SQL                                    
--          Microsoft TPC-E Benchmark Kit Ver. 1.14.0-1031      
--          Copyright Microsoft, 2008-2015                  
--                                                              
--  Implements TPC-E atomicity rollback test                    
--                                                              
----------------------------------------------------------------
USE tpce
GO

DECLARE @num_custs      bigint,
        @num_companies  bigint,
        @acct_id        bigint,
        @cust_id        bigint,
        @company_id     bigint,
        @symbol         varchar(15),
        @trade_id       bigint

--- get the number of customers in the database
SELECT  @num_custs      = COUNT_BIG(*) FROM CUSTOMER
SELECT  @num_companies  = COUNT_BIG(*) FROM COMPANY

--- select a random customer ID for use in the test
SET     @cust_id        = CONVERT(bigint, RAND() * 10 * @num_custs) + 43000000001

--- select a random customer account ID for the customer selected above
SELECT  TOP 1
        @acct_id        = CA_ID
FROM    CUSTOMER_ACCOUNT
WHERE   CA_C_ID         <= @cust_id
ORDER   BY 1 DESC

--- select a random company for use in the test
SET     @company_id     = CONVERT(bigint, RAND() * @num_companies) + 4300000001

--- select the symbol for this company
SELECT  TOP 1
        @symbol     = S_SYMB
FROM    SECURITY JOIN COMPANY ON S_CO_ID = CO_ID
WHERE   CO_ID = @company_id

EXEC TradeOrder_RB  @acct_id,
                    @symbol,
                    @trade_id   OUTPUT

--- verify the rows were not inserted
PRINT   'ROW FROM TRADE'
PRINT   '--------------'
SELECT  *
FROM    TRADE
WHERE   T_ID = @trade_id
                        
PRINT   'ROW FROM TRADE_HISTORY'
PRINT   '----------------------'
SELECT  *
FROM    TRADE_HISTORY
WHERE   TH_T_ID = @trade_id
 
