Chapter 16 Textbook Assignment

Purpose:  To create scripts that include transaction processing

To see a video demonstration of all tasks in the assignment, view: https://youtu.be/tphkJzdMLtY

Task #1:  Transaction Processing

Scenario:  1. Our boss would like us to use transaction processing to update invoice and invoicelineitem data.

1.  Launch SSMS, create a new query and enter the comment /* Task 1 */

2.  We will need to use the AP database and insert the code displayed below to add an invoice for VendorID 34 and to add 2 lines items for that invoice.

USE AP;
GO
DECLARE @InvoiceID int;
    BEGIN TRY
        BEGIN TRAN;
            INSERT Invoices VALUES (34,'ZXA-080','2020-04-30',14092.59,0,0,3,'2020-05-30',NULL);
            SET @InvoiceID = @@IDENTITY;
            INSERT InvoiceLineItems VALUES (@InvoiceID,1,160,4447.23,'HW upgrade');
            INSERT InvoiceLineItems VALUES (@InvoiceID,2,160,9645.36,'OS upgrade');
        COMMIT TRAN;
    END TRY
BEGIN CATCH
    ROLLBACK TRAN;
    PRINT 'FAILURE: Order was not processed.'; PRINT 'Error ' + CONVERT(varchar, ERROR_NUMBER(), 1) + ': ' + ERROR_MESSAGE();
END CATCH;
GO
SELECT * FROM Invoices WHERE VendorID=34;

3.  Comment out the code and save the file as chapter16-textbook-assignment.sql

Scenario:  Our boss wants a short transaction processing script to delete 1 invoice for a specific vendor If more than 1 invoice is deleted, they want us to backout of the update.

1.  We don't really need to insert his inside a TRY... CATCH because we will be using ROLLBACK if more than 1 invoice is deleted.  The only reason to use TRY... CATCH would be for an unforseen error

BEGIN TRAN;
    DELETE Invoices WHERE VendorID = 34;
    IF @@ROWCOUNT > 1
        BEGIN
            ROLLBACK TRAN;
            PRINT 'More invoices than expected. Deletions rolled back.';
        END;
    ELSE
        BEGIN
            COMMIT TRAN;
            PRINT 'Deletions committed to the database.';
        END;

2. Comment out the code and save your changes.

Scenario:  Our boss would like us to create a transaction that makes a temporary table and deletes multiple vendors from the table. Our boss would like us to Save transaction points so some of the updates can be successful if there are errors

1.  Enter the following script:

USE AP;
GO
IF OBJECT_ID('tempdb..#VendorCopy') IS NOT NULL
    DROP TABLE tempdb.. #VendorCopy;

SELECT VendorID, VendorName INTO #VendorCopy
FROM Vendors WHERE VendorID < 5;

BEGIN TRAN;
    DELETE #VendorCopy WHERE VendorID = 1;
    SAVE TRAN Vendor1;
            DELETE #VendorCopy WHERE VendorID = 2;
            SAVE TRAN Vendor2;
                DELETE #VendorCopy WHERE VendorID = 3;
                SELECT * FROM #VendorCopy;  -- this will only show 1 vendor

            ROLLBACK TRAN Vendor2;
            SELECT * FROM #VendorCopy;  -- this will show 2 vendors
    ROLLBACK TRAN Vendor1;
    SELECT * FROM #VendorCopy; -- this will show 3 vendors
COMMIT TRAN;
SELECT * FROM #VendorCopy;

2. Comment out the code and save your changes.

3.  Upload the file to the dropbox in Moodle