Question

Please Answer the questions listed in the comment section, 1 and 2!

-- ====================================================================
-- SQL Script for Bookstore Database Records
--    This script demonstrates the use of transactions for Unit 13 Exercise
--
--      Created by: Jennifer Rosato
--      Created on: 12/2013
--      Modified by: David Vosen
--      Modified on: 11/2016
--      Modified on: 11/2018 Add DBCC LOG(CIS_3107_##, 1) to view 
--      transactions.
-- ==================================================================== 

--SELECT * FROM customer;
--SELECT * FROM invoice;
--SELECT * FROM line;
--SELECT * FROM product;
-- cus_code for the invoice order: 1000006
-- inv_number for the new invoice: 4000006
-- p_code for the product ordered: 3000007


-- example that rolls back the transaction
BEGIN TRANSACTION;                      -- the DBMS starts keeping a transaction log here

        -- verify that the new records in invoice and line are NOT there
        SELECT * FROM invoice WHERE cus_code = '1000006';
        SELECT * FROM line WHERE inv_number = '4000006';
        SELECT * FROM product WHERE p_code = '3000007';
        
        -- add an invoice using the current date
        INSERT INTO invoice
        VALUES ('4000006', '1000006', GETDATE());

        -- add a line item to the invoice
        INSERT INTO line
        VALUES ('5000011', '4000006', '3000007', 1, 29.99);

        -- update the product table to remove the amount of the product 
        -- that was sold
        UPDATE product
        SET p_qoh = p_qoh - 1
        WHERE p_code = 3000007;
        
        -- verify that the new records in invoice and line are there
        SELECT * FROM invoice WHERE cus_code = '1000006';
        SELECT * FROM line WHERE inv_number = '4000006';
        SELECT * FROM product WHERE p_code = '3000007';

ROLLBACK;

-- verify that the new records in invoice and line are no longer there and that 
-- the original amount of the product on hand is the same
SELECT * FROM invoice WHERE cus_code = '1000006';
SELECT * FROM line WHERE inv_number = '4000006';
SELECT * FROM product WHERE p_code = '3000007';

-- example that commits the transaction
BEGIN TRANSACTION;                      -- the DBMS starts keeping a transaction log here
        
        -- add an invoice using the current date
        INSERT INTO invoice
        VALUES (4000006, 1000006, GETDATE());

        -- add a line item to the invoice
        INSERT INTO line
        VALUES (5000011, 4000006, 3000007, 1, 29.99);

        -- update the product table to remove the amount of the product 
        -- that was sold
        UPDATE product
        SET p_qoh = p_qoh - 1
        WHERE p_code = 3000007;
        
        -- verify that the new records in invoice and line are there
        SELECT * FROM invoice WHERE cus_code = '1000006';
        SELECT * FROM line WHERE inv_number = '4000006';
        SELECT * FROM product WHERE p_code = '3000007';

COMMIT; 


-- DBCC LOG(CIS_3107_00, 1)

/*
DBCC LOG(yourdatabasename, typeofoutput) where typeofoutput:
0: Return only the minimum of information for each operation -- the operation, its context and the transaction ID. (Default)
1: As 0, but also retrieve any flags and the log record length.
2: As 1, but also retrieve the object name, index name, page ID and slot ID.
3: Full informational dump of each operation.
4: As 3 but includes a hex dump of the current transaction log row.
*/



-- 1. create YOUR OWN example that rolls back the transaction 
BEGIN TRANSACTION;                      -- the DBMS starts keeping a transaction log here

        -- verify that your new records in invoice and line are NOT there 
        SELECT * FROM  WHERE 
        SELECT * FROM  WHERE 
        SELECT * FROM  WHERE    
        
        -- add an invoice using the current date
        INSERT INTO  
        VALUES 

        -- add a different line item to the invoice
        INSERT INTO  
        VALUES 

        -- update the product table to remove the amount of the product 
        -- that was sold for that line item
        UPDATE  
        SET 
        WHERE 
        
        -- verify that your new records in invoice and line are there 
        SELECT * FROM  WHERE 
        SELECT * FROM  WHERE 
        SELECT * FROM  WHERE 

ROLLBACK;
 
-- 2. Now use your example to commit your transaction now that you have tested it
BEGIN TRANSACTION;                      -- the DBMS starts keeping a transaction log here
        
        -- add the invoice using the current date 

        -- add your line item to the invoice
 

        -- update the product table to remove the amount of the product 
        -- that was sold
 
    -- verify that the changes have been made to the tables

COMMIT; 

Answer 1

SELECT * FROM invoice WHERE cus_code = '4000010'; 
SELECT * FROM line WHERE inv_number = '1004030'; 
SELECT * FROM product WHERE p_code = '677919'; 

BEGIN TRANSACTION; 

INSERT INTO invoice VALUES ('1004030', '4000010', '03-08-2019'); 
INSERT INTO line VALUES ('129391', '1004030', '677919', 1, 29.99); 

UPDATE product SET p_qoh = p_qoh - 1 WHERE p_code = 677919; 
SELECT * FROM invoice WHERE cus_code = '4000010'; 
SELECT * FROM line WHERE inv_number = '1004030'; 
SELECT * FROM product WHERE p_code = '677919'; 
ROLLBACK;


SELECT * FROM invoice WHERE cus_code = '4000010';
SELECT * FROM line WHERE inv_number = '1004030';
SELECT * FROM product WHERE p_code = '677919';

BEGIN TRANSACTION;                     
        
        INSERT INTO invoice VALUES ('1004030', '4000010', '03-08-2019');

        INSERT INTO line VALUES ('129391', '1004030', '677919', 1, 29.99);

        UPDATE product
        SET p_qoh = p_qoh - 1
        WHERE p_code = 677919;
        
        -- verify that the new records in invoice and line are there
        SELECT * FROM invoice WHERE cus_code = '4000010';
        SELECT * FROM line WHERE inv_number = '1004030';
        SELECT * FROM product WHERE p_code = '677919';

COMMIT;