111
ON DELETE CASCADE
ON UPDATE CASCADE
ALTER TABLE Products
ADD CONSTRAINT R_4 FOREIGN KEY (CategoryId) REFERENCES Catego-
ries(CategoryId)
ON DELETE CASCADE
ON UPDATE CASCADE
Ниже приведено создание связей между таблицами состава заказа и
самого заказа, а также с таблицей продукции.
ALTER TABLE Purchases
ADD CONSTRAINT R_12 FOREIGN KEY (OrderId) REFERENCES Or-
ders(OrderId)
ON DELETE CASCADE
ON UPDATE CASCADE
ALTER TABLE Purchases
ADD CONSTRAINT R_13 FOREIGN KEY (ProductId) REFERENCES Prod-
ucts(ProductId)
ON DELETE CASCADE
ON UPDATE CASCADE
Создадим функцию для авторизации менеджера:
CREATE FUNCTION LoginAdmin(@email nvarchar(255), @password nvar-
char(18))
RETURNS int
AS
BEGIN
DECLARE @result int
SET @result = (SELECT TOP(1) AdminId
FROM dbo.Admins
WHERE Email = @email AND Password = @password)
IF @result Is NULL
BEGIN
SET @result = -1
END
RETURN (@result)
END
112
Создадим функцию для авторизации клиента:
CREATE FUNCTION LoginClient(@email nvarchar(255), @password nvarchar(18)
RETURNS int
AS
BEGIN
DECLARE @result int
SET @result = (SELECT TOP(1) ClientId
FROM dbo.Clients
WHERE Email = @email AND Password = @password)
IF @result Is NULL
BEGIN
SET @result = -1
END
RETURN (@result)
END
Создадим функцию для подсчета общей стоимости в корзине клииента:
CREATE FUNCTION [dbo].[SumCart]
(
@ClientId int
)
RETURNS decimal
AS
BEGIN
DECLARE @RES DECIMAL
SET @RES=(SELECT SUM(dbo.Products.Price * dbo.Carts.Amount)
FROM dbo.Carts INNER JOIN
dbo.Products ON dbo.Carts.ProductId = dbo.Products.ProductId
WHERE (dbo.Carts.ClientId = @ClientId))
IF @RES IS NULL
BEGIN
SET @RES=0
END
RETURN @RES
END
