May 6, 2004 at 6:59 pm
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)
May 7, 2004 at 2:01 am
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
May 7, 2004 at 5:21 pm
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