output.log

Resetting...
Resetting stock for product 22 to 104 units.

(1 row(s) affected)
Removing order (OrderID=11077, ProductID=22).

(1 row(s) affected)
Adding order to Order Details table for 50 units of product 22.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Test 1: Was UnitsInStock properly decremented for product 22?
Test 1 passed.
Test 2: Is the order actually in Order Details?
Test 2 passed.
Msg 2812, Level 16, State 62, Line 35
Could not find stored procedure 'lab8_yipdw_reset'.
Test 3: Are unfillable orders rejected?
Insuffcient quantity in stock

(1 row(s) affected)
Test 3 failed - the order was placed even though there are not enough units in stock to fill the order.
Test 4: Are unfillable orders prevented from modifying the number of units in stock?
Test 4 failed -- an order for product 22 that could not be filled has somehow changed the number of units in stock for product 22.
Msg 2812, Level 16, State 62, Line 62
Could not find stored procedure 'lab8_yipdw_reset'.
Msg 3701, Level 11, State 5, Line 63
Cannot drop the procedure 'lab8_yipdw_reset', because it does not exist or you do not have permission.
Not all tests were successful.

trigger-check.sql

USE NWindsanderib42;

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'lab8_yipdw_reset' AND type = 'P')
BEGIN
DROP PROCEDURE lab8_yipdw_reset
END
GO

CREATE PROCEDURE lab8_yipdw_reset
AS

BEGIN

PRINT 'Resetting...'
PRINT 'Resetting stock for product 22 to 104 units.'
UPDATE Products SET UnitsInStock=104 WHERE ProductID=22;
PRINT 'Removing order (OrderID=11077, ProductID=22).'
DELETE FROM [Order Details] WHERE OrderID=11077 AND ProductID=22; 

END

GO

DECLARE @t1 AS INTEGER, @fail AS TINYINT
SELECT @fail=0;
EXECUTE lab8_yipdw_reset
PRINT 'Adding order to Order Details table for 50 units of product 22.'
INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (11077, 22, 21.00, 50, 0.0) 
SELECT @t1=UnitsInStock FROM [Products] WHERE ProductID=22;

-- Check if UnitsInStock count was properly decremented.
PRINT 'Test 1: Was UnitsInStock properly decremented for product 22?'
IF @t1 = 54 
BEGIN
PRINT 'Test 1 passed.'
END
ELSE
BEGIN
PRINT 'Test 1 failed - I inserted an order for 50 units of Product 22, but the change was not made in the Products table.'
SELECT @fail = 1;
END

-- Check to see if the order actually exists in the Order Details table.
PRINT 'Test 2: Is the order actually in Order Details?'
SELECT @t1=COUNT(ProductID) FROM [Order Details] WHERE OrderID=11077 AND ProductID=22
IF @t1 = 1
BEGIN
PRINT 'Test 2 passed.'
END
ELSE
BEGIN
PRINT 'Test 2 failed - the order does not appear in the Order Details table.'
SELECT @fail = 1;
END

-- Ensure that the order does not go through if we order too much.
EXECUTE lab8_yipdw_reset
PRINT 'Test 3: Are unfillable orders rejected?'
INSERT INTO [Order Details] (OrderID, ProductID, UnitPrice, Quantity, Discount) VALUES (11077, 22, 21.00, 9999, 0.0) 
SELECT @t1=COUNT(ProductID) FROM [Order Details] WHERE OrderID=11077 AND ProductID=22
IF @t1 = 0
BEGIN
PRINT 'Test 3 passed.'
END
ELSE
BEGIN
PRINT 'Test 3 failed - the order was placed even though there are not enough units in stock to fill the order.'
SELECT @fail = 1;
END

-- Ensure that the original UnitsInStock remains unaffected after unsuccessful order.
PRINT 'Test 4: Are unfillable orders prevented from modifying the number of units in stock?'
SELECT @t1=UnitsInStock FROM [Products] WHERE ProductID=22;
IF @t1 = 104
BEGIN
PRINT 'Test 4 passed.'
END
ELSE
BEGIN
PRINT 'Test 4 failed -- an order for product 22 that could not be filled has somehow changed the number of units in stock for product 22.'
SELECT @fail = 1;
END

EXECUTE lab8_yipdw_reset
DROP PROCEDURE lab8_yipdw_reset

IF @fail = 0
BEGIN
PRINT 'All tests passed.'
END
ELSE
BEGIN
PRINT 'Not all tests were successful.'
END