Check?Trigger?or??????

  • Sorry Guys I am new to this can anyone help?

    How could I do this?

    I would like to be able to perform a check on a field called Quantity in OrderDetails Table. I want it so when the user enters a quantity for a sale it checks the Stock table's Quantity to assure there is enough in stock. Then if there is enough in Stock update the stock quantity to account for amount sold or entered into OrderDetails Quantity

    Here is what I have so far

    -----------------------------------------------

    USE SpicesDB

    IF EXISTS (SELECT name FROM sysobjects

                WHERE name = 'Employees')

       DROP TABLE Employees

    GO

    CREATE TABLE Employees

    (

     EmployeeID   VARCHAR(10)  NOT NULL,

     FirstName VARCHAR(20)  NOT NULL,

     LastName VARCHAR(20)  NOT NULL,

     Title  VARCHAR(30)  NOT NULL,

     Salary DECIMAL(10, 2)  NOT NULL Check (Salary >= 0.00 AND Salary < 300000.00),

     ComRate DECIMAL(10, 2)  NOT NULL Check (ComRate >=0.00 AND ComRate < 0.20)

    )

    GO

    IF EXISTS (SELECT name FROM sysobjects

                WHERE name = 'Customers')

       DROP TABLE Customers

    GO

    CREATE TABLE Customers

    (

     CustomerID   VARCHAR(10)  NOT NULL,

     Company VARCHAR(20)  NOT NULL,

     Address VARCHAR(50)  NOT NULL,

     City  VARCHAR(20)  NOT NULL,

     State VARCHAR(2)  NOT NULL,

     ZipCode VARCHAR(5)  NOT NULL,

     LocationID VARCHAR(2)  NOT NULL,

     ContactName  VARCHAR(20)  NOT NULL,

     ContactPhone VARCHAR(20)  NOT NULL,

     ContactFax VARCHAR(20)  NOT NULL 

    )

    GO

    IF EXISTS (SELECT name FROM sysobjects

                WHERE name = 'Products')

       DROP TABLE Products

    GO

    CREATE TABLE Products

    (

     ProductID   VARCHAR(10)  NOT NULL,

     ProductName VARCHAR(30)  NOT NULL,

     Description VARCHAR(50)  NOT NULL,

     QtyPerUnit  INT  NOT NULL,

     ReorderLevel INT  NOT NULL, Check (ReorderLevel >= 1000 And ReorderLevel <= 2000),

     Discontinued VARCHAR(12)  NOT NULL,

     MaxStockQTY INT  NOT NULL Check (MaxStockQTY >= 3000 And MaxStockQTY <= 5000)

    )

    GO

    IF EXISTS (SELECT name FROM sysobjects

                WHERE name = 'Orders')

       DROP TABLE Orders

    GO

    CREATE TABLE Orders

    (

     OrderID   INT  NOT NULL,

     EmployeeID VARCHAR(10)  NOT NULL,

     CustomerID VARCHAR(10)  NOT NULL,

     SupplierID  VARCHAR(10)  NOT NULL,

     OrderDate VARCHAR(20)  NOT NULL,

     RequiredDate VARCHAR(20)  NOT NULL,

     ShippedDate VARCHAR(20)  NOT NULL

    )

    GO

    IF EXISTS (SELECT name FROM sysobjects

                WHERE name = 'OrderDetails')

       DROP TABLE OrderDetails

    GO

    CREATE TABLE OrderDetails

    (

     OrderDetailID INT NOT NULL,

     OrderID   INT  NOT NULL,

     Line INT NOT NULL,

     ProductID VARCHAR(10)  NOT NULL,

     UnitPrice DECIMAL(10, 2)  NOT NULL Check (UnitPrice >= 39.99),

     Quantity  INT  NOT NULL, 

     StockID INT  NOT NULL

    )

    GO

    IF EXISTS (SELECT name FROM sysobjects

                WHERE name = 'Stock')

       DROP TABLE Stock

    GO

    CREATE TABLE Stock

    (

     StockID   INT  NOT NULL,

     ProductID VARCHAR(10)  NOT NULL,

     Country VARCHAR(20)  NOT NULL,

     Quantity  INT  NOT NULL check (Quantity >= 0 And Quantity <= 5000)

    )

    GO

    IF EXISTS (SELECT name FROM sysobjects

                WHERE name = 'Location')

       DROP TABLE Location

    GO

    CREATE TABLE Location

    (

     LocationID   VARCHAR(2)  NOT NULL,

     Country VARCHAR(20)  NOT NULL,

    )

    GO

    INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('AG00000001', 'Allen', 'Garner', 'President', '250000.00', '0.10');

    INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('BB00000002', 'Bobby', 'Brown', 'Vice President', '200000.00', '0.10');

    INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('CC00000003', 'Chester', 'Colby', 'Sales manager', '80000.00', '0.08');

    INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('DD00000004', 'David', 'Doyle', 'Salesman', '60000.00', '0.06');

    INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('EE00000005', 'Evan', 'Evers', 'Salesman', '60000.00', '0.06');

    INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('BF00000006', 'Ben', 'Franklin', 'Salesman', '60000.00', '0.06');

    INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('GG00000007', 'George', 'Gershwin', 'Salesman', '60000.00', '0.06');

    INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('MG00000008', 'Mel', 'Gibson', 'Salesman', '60000.00', '0.06');

    INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('PG00000009', 'Paul', 'McCartney', 'Salesman', '60000.00', '0.06');

    INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('MP00000010', 'Matt', 'Perry', 'Salesman', '60000.00', '0.06');

    INSERT INTO Employees (EmployeeID, FirstName, LastName, Title, Salary, ComRate) VALUES ('LS00000011', 'Luke', 'Skywalker', 'Salesman', '60000.00', '0.06');

    GO

    INSERT INTO Customers (CustomerID, Company, Address, City, State, ZipCode, LocationID, ContactName, ContactPhone, ContactFax) VALUES ('AS00000001', 'American Spices', '1231 Main St.', 'Vancouver', 'WA', '98664', 'US', 'John Smith', '360-604-3211', '360-604-3212');

    INSERT INTO Customers (CustomerID, Company, Address, City, State, ZipCode, LocationID, ContactName, ContactPhone, ContactFax) VALUES ('FS00000002', 'French Spice Connect', '1232 Main St.', 'Camas', 'WA', '98607', 'US', 'Mike Smith', '360-834-3213', '360-834-3214');

    INSERT INTO Customers (CustomerID, Company, Address, City, State, ZipCode, LocationID, ContactName, ContactPhone, ContactFax) VALUES ('AU00000003', 'Australian Spice Co.', '1233 Main St.', 'Battleground', 'WA', '98667', 'US', 'Bob Smith', '360-696-3215', '360-696-3216');

    INSERT INTO Customers (CustomerID, Company, Address, City, State, ZipCode, LocationID, ContactName, ContactPhone, ContactFax) VALUES ('CA00000004', 'Canadian Spice Co.', '1234 Main St.', 'Washougal', 'WA', '98607', 'US', 'Randy Smith', '360-834-3217', '360-834-3218');

    GO

    INSERT INTO Products (ProductID, ProductName, Description, QtyPerUnit, ReorderLevel, Discontinued, MaxStockQty) VALUES ('SBCinamin', 'Cinamin', '12 oz bottles', '24', '1000', 'Active', '5000');

    INSERT INTO Products (ProductID, ProductName, Description, QtyPerUnit, ReorderLevel, Discontinued, MaxStockQty) VALUES ('SBGarlic', 'Garlic', '12 oz bottles', '24', '1000', 'Active', '5000');

    INSERT INTO Products (ProductID, ProductName, Description, QtyPerUnit, ReorderLevel, Discontinued, MaxStockQty) VALUES ('SBOregano', 'Oregano', '12 oz bottles', '24', '1000', 'Active', '5000');

    INSERT INTO Products (ProductID, ProductName, Description, QtyPerUnit, ReorderLevel, Discontinued, MaxStockQty) VALUES ('SBThyme', 'Thyme', '12 oz bottles', '24', '1000', 'Active', '5000');

    INSERT INTO Products (ProductID, ProductName, Description, QtyPerUnit, ReorderLevel, Discontinued, MaxStockQty) VALUES ('SBBay', 'Bay leaves', '12 oz bottles', '24', '1000', 'Active', '5000');

    INSERT INTO Products (ProductID, ProductName, Description, QtyPerUnit, ReorderLevel, Discontinued, MaxStockQty) VALUES ('SBChili', 'Chili Powder', '12 oz bottles', '24', '1000', 'Active', '5000');

    GO

    INSERT INTO Stock (StockID, ProductID, Country, Quantity) VALUES ('1', 'SBCinamin', 'US', '2000');

    INSERT INTO Stock (StockID, ProductID, Country, Quantity) VALUES ('2', 'SBGarlic', 'US', '3000');

    INSERT INTO Stock (StockID, ProductID, Country, Quantity) VALUES ('3', 'SBOregano', 'US', '2000');

    INSERT INTO Stock (StockID, ProductID, Country, Quantity) VALUES ('4', 'SBThyme', 'US', '3000');

    INSERT INTO Stock (StockID, ProductID, Country, Quantity) VALUES ('5', 'SBBay', 'US', '2000');

    INSERT INTO Stock (StockID, ProductID, Country, Quantity) VALUES ('6', 'SBChili', 'US', '3000');

    GO

    INSERT INTO Orders (OrderID, EmployeeID, CustomerID, SupplierID, OrderDate, RequiredDate, ShippedDate) VALUES ('1', 'AG00000001', 'AS00000001', 'GS00000001', '12/06/2003', '12/30/2003', '12/15/2003');

    GO

    INSERT INTO OrderDetails (OrderDetailID, OrderID, Line, ProductID, UnitPrice, Quantity, StockID) VALUES ('1', '1', '1', 'SBCinamin', '49.99', '150', '1');

    INSERT INTO OrderDetails (OrderDetailID, OrderID, Line, ProductID, UnitPrice, Quantity, StockID) VALUES ('2', '1', '2', 'SBGarlic', '49.99', '150', '2');

    INSERT INTO OrderDetails (OrderDetailID, OrderID, Line, ProductID, UnitPrice, Quantity, StockID) VALUES ('3', '1', '3', 'SBOregano', '49.99', '150', '3');

    INSERT INTO OrderDetails (OrderDetailID, OrderID, Line, ProductID, UnitPrice, Quantity, StockID) VALUES ('4', '1', '4', 'SBThyme', '49.99', '150', '4');

    INSERT INTO OrderDetails (OrderDetailID, OrderID, Line, ProductID, UnitPrice, Quantity, StockID) VALUES ('5', '1', '5', 'SBBay', '49.99', '150', '5');

    INSERT INTO OrderDetails (OrderDetailID, OrderID, Line, ProductID, UnitPrice, Quantity, StockID) VALUES ('6', '1', '6', 'SBChili', '49.99', '150', '6');

    Go

    INSERT INTO Location (LocationID, Country) VALUES ('US', 'UnitedStates');

    INSERT INTO Location (LocationID, Country) VALUES ('AF', 'Africa');

    INSERT INTO Location (LocationID, Country) VALUES ('GM', 'Germany');

    INSERT INTO Location (LocationID, Country) VALUES ('JP', 'Japan');

    INSERT INTO Location (LocationID, Country) VALUES ('CA', 'Canada');

    INSERT INTO Location (LocationID, Country) VALUES ('SP', 'Spain');

    GO

    ALTER TABLE Employees

     ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID)

     

    GO

    ALTER TABLE Customers

     ADD CONSTRAINT PK_CustomerID PRIMARY KEY (CustomerID)

    GO

    ALTER TABLE Products

     ADD CONSTRAINT PK_ProductID PRIMARY KEY (ProductID)

    GO

    ALTER TABLE Orders

     ADD CONSTRAINT PK_OrderID PRIMARY KEY (OrderID)

     

    GO

    ALTER TABLE OrderDetails

     ADD CONSTRAINT PK_OrderDetailsID PRIMARY KEY (OrderDetailID)

    GO

    ALTER TABLE Stock

     ADD CONSTRAINT PK_StockID PRIMARY KEY (StockID)

    GO

    ALTER TABLE Location

     ADD CONSTRAINT PK_LocationID PRIMARY KEY (locationID)

    GO

    ALTER TABLE Orders

     ADD CONSTRAINT fk_Orders_Employees

     FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)

     

    GO

    ALTER TABLE Orders

     ADD CONSTRAINT fk_Orders_Customer

     FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

     

    GO

    ALTER TABLE OrderDetails

     ADD CONSTRAINT fk_Orders_OrderDetails

     FOREIGN KEY (OrderID) REFERENCES Orders(OrderID)

     

    GO

    ALTER TABLE OrderDetails

     ADD CONSTRAINT fk_Orders_Stock

     FOREIGN KEY (StockID) REFERENCES Stock(StockID)

     

    GO

    ALTER TABLE Stock

     ADD CONSTRAINT fk_Stock_Products

     FOREIGN KEY (ProductID) REFERENCES Products(ProductID)

     

    GO

    ALTER TABLE Customers

     ADD CONSTRAINT fk_Customers_Location

     FOREIGN KEY (LocationID) REFERENCES Location(LocationID)

     

  • I would like to be able to perform a check on a field called Quantity in OrderDetails Table. I want it so when the user enters a quantity for a sale it checks the Stock table's Quantity to assure there is enough in stock. Then if there is enough in Stock update the stock quantity to account for amount sold or entered into OrderDetails Quantity

    CREATE TRIGGER TRIG_UPDATE_STOCK_TABLE

    ON OrderDetails

    FOR INSERT

    AS

    Declare @OrderQuantity int

    Declare @StockQuantity int

    IF UPDATE(Quantity)

    BEGIN

    SET @OrderQuantity = (Select i.Quantity From inserted i)

    SET @StockQuantity = (Select Quantity From Stock s, inserted i 

    Where s.ProductID = i.ProductID)

    IF (@OrderQuantity < @StockQuantity)

         BEGIN

                --Update the Stock Table Before placing order?

                UPDATE STOCK

                 SET Quantity = @StockQuantity - @OrderQuantity

                 FROM STOCK, inserted i

                 WHERE STOCK.ProcuctID = i.ProductID

          END

    ELSE

         BEGIN

                 RAISERROR ('The Quantity of ' + i.ProductID + ' is not      sufficient', 16, 1)  --or something to that effect

         END

    END

    I have not tested this so you will probably have to tweek.  But it should work something like this.

    Pete

     

  • Thanks for the help very Appreciated

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply