Question

Implement a stored procedure to handle a complete transaction. Also implement a trigger to validate data before a transaction is permitted to execute.

Schema:

Customer Table

CustomerID (PK).

FirstName (NOT NULL).

LastName (NOT NULL).

Address.

City.

State.

Zip.

Country.

Phone.

Email (UNIQUE and NOT NULL).

Username (UNIQUE and NOT NULL).

Password (NOT NULL).

Order Table

OrderID (PK).

CustomerID (FK).

OrderDate.

ShipDate.

Order Detail Table

OrderDetailID (PK).

OrderID (FK).

ProductID (FK).

Price.

Quantity (CHECK > 0).

Product Table

ProductID (PK).

CategoryID (FK).

ProductName.

ProductDescription.

UnitPrice.

Picture.

Category Table

CategoryID (PK).

CategoryName.

Description.

This is what I have to complete: I really just need Part 2 completed.

Part 1:

Part 1: Database Stored Procedure and Testing Important: The stored procedure you create should include the following steps in the transaction:

1. Set a save point for the transaction.

2. Insert into the order table to include the customer ID while generating the order ID.

3. Insert into the order detail table to include the order id, product ID, and quantity.

4. Update product table to deduct the quantity. 5. Commit transaction.

Complete the following:

1. Create a stored procedure (write code) that takes in the customer ID, product ID, and quantity and executes the transaction.

2. Test the three executions of the stored procedure (that include valid and invalid data) to help test extraneous information.

My code for this:

Use SmartHomes

Go

Create Procedure orders

       @CustomerID int,

       @ProductID int,

       @Quantity float

As

Begin

Begin Transaction;

Declare @OrderID int;

Select MAX(OrderID) + 1 From Order1;

Insert into Order1 (OrderID, CustomerID)

Values (@OrderID, @CustomerID);

Insert into OrderDetail(OrderID, ProductID, Quantity)

Values(@OrderID, @ProductID, @Quantity);

End

Part 2:

To ensure data integrity, we need to validate that the existing quantity of the product table has enough items before the stored procedure is executed. This can be done using a trigger. Complete these steps to create a trigger:

Your trigger should execute the following steps in the transaction: Check if the current quantity of the product being passed in is greater than or equal to the entered amount. If yes, update the quantity in the product table to deduct the entered value. If not, throw an exception so that the transaction is not committed.

Create a trigger based on the update of the product table to check if the current quantity has enough items to deduct the updated quantity. Paste trigger code below. Test three executions of the trigger with various inputs (that include valid and invalid data) to test for extraneous information.

Answer 1

Short Summary:

• Shown all the SQL queries that I used to achieve the result
• You have to add Quantity column in Product table
• Created the stored procedure and trigger and shown you the test cases.

**************Please upvote the answer and appreciate our time.************

SQL Queries to CREATE TABLE:

CREATE DATABASE SmartHomes
GO

USE SmartHomes
GO

CREATE TABLE Customer(
   CustomerID INT PRIMARY KEY
   , FirstName VARCHAR(50) NOT NULL
   , LastName VARCHAR(50) NOT NULL
   , Address VARCHAR(50)
   , City VARCHAR(50)
   , State VARCHAR(50)
   , Zip VARCHAR(50)
   , Country VARCHAR(50)
   , Phone VARCHAR(50)
   , Email VARCHAR(50) NOT NULL UNIQUE
   , Username VARCHAR(50) NOT NULL UNIQUE
   , Password VARCHAR(50) NOT NULL
)

CREATE TABLE Category(
   CategoryID INT PRIMARY KEY
   , CategoryName VARCHAR(50)
)

CREATE TABLE Product(
   ProductID INT PRIMARY KEY
   , CategoryID INT FOREIGN KEY REFERENCES Category(CategoryID)
   , ProductName VARCHAR(50)
   , ProductDescription VARCHAR(50)
   , UnitPrice DECIMAL(18, 2)
   , Picture Image
   , Quantity INT
)

CREATE TABLE Order1(
   OrderID INT PRIMARY KEY
   , CustomerID INT FOREIGN KEY REFERENCES Customer(CustomerID)
   , OrderDate DATETIME
   , ShipDate DATETIME
)

CREATE TABLE OrderDetail(
   OrderDetailID INT PRIMARY KEY
   , OrderID INT FOREIGN KEY REFERENCES Order1(OrderID)
   , ProductID INT FOREIGN KEY REFERENCES Product(ProductID)
   , Price DECIMAL(18, 2)
   , Quantity INT
   , CHECK (Quantity > 0)
)

*************************************************************************************************

Stored procedure:

USE [SmartHomes]
GO

CREATE PROCEDURE [dbo].[orders]
@CustomerID INT,
@ProductID INT,
@Quantity FLOAT
AS
BEGIN
   SET NOCOUNT ON;
   BEGIN TRANSACTION;
       DECLARE @OrderID INT;
       SELECT @OrderID = MAX(OrderID) + 1 FROM Order1;

       IF @OrderID IS NULL
           SET @OrderID = 1

       INSERT INTO Order1 (OrderID, CustomerID) VALUES (@OrderID, @CustomerID);
       INSERT INTO OrderDetail(OrderID, ProductID, Quantity) VALUES(@OrderID, @ProductID, @Quantity);

       UPDATE Product SET Quantity = Quantity-@Quantity WHERE ProductID = @ProductID
   COMMIT TRANSACTION
END
GO

*************************************************************************************************

Trigger:

USE [SmartHomes]
GO

CREATE TRIGGER [dbo].[trg_product_audit]
ON [dbo].[Product]
AFTER INSERT, UPDATE
AS
BEGIN
   SET NOCOUNT ON;

   -- Find the new quantity
   DECLARE @newQty INT
   SELECT @newQty = i.Quantity FROM inserted AS i
  
   IF (@newQty < 0)
   BEGIN
       -- rollback and end the transaction inside the trigger   
       ROLLBACK TRAN ;
        -- raise an error
       RAISERROR ( 'The transaction is NOT Committed! Quantity is not valid', 16, 1) ;
END
END
GO

ALTER TABLE [dbo].[Product] ENABLE TRIGGER [trg_product_audit]
GO

*************************************************************************************************

Test:

Assume this product :

Execute the stored procedure to order 10 quantities

EXEC orders 1, 1, 10

Verify the Orders and product table: (Quantity is not updated in product table, no record inserted into Order and orderdetails tables)

*************************************************************************************************

Place order for iphone 11 with Quantity 2

EXEC orders 1, 1, 2

Verify the Orders and product table:

**************************************************************************************

Feel free to rate the answer and comment your questions, if you have any.

Please upvote the answer and appreciate our time.

Happy Studying!!!

**************************************************************************************