--create database magazin.net
 
use [magazin.net]
go
 
create table [customers]
(
[ID] INT NOT NULL IDENTITY ,
[FName] nvarchar(20) null,
[MName] nvarchar(20) null,
[LName] nvarchar(20) null,
[address] nvarchar(50) null,
[Citu] nvarchar(20) null,
[Phone] nvarchar(12) null,
[DateInSystem] date DEFAULT GETDATE() 
) 
 
create table [products]
(
[ID] INT NOT NULL IDENTITY ,
[Name] nvarchar(50) not null ,
 
) 
 
create table [products_details]
(
[ID] INT NOT NULL,
[tupe] varchar(20) ,
[Naimenovanie] varchar(100) not null,
[description] varchar(500) not null,
[data_zavoza] date not null
)
 
create table [stock]
(
[ProductID] INT NOT NULL,
[Qtu] int DEFAULT 0 ,
[Name_Edinitsy] varchar(30) not null,
[prise] money NOT NULL ,
[total_prise] AS CONVERT ( money, Qtu*prise)
)
 
create table [order]
(
[ID] INT NOT NULL IDENTITY ,
[customersID] INT NULL ,
[OrderDate] date DEFAULT GETDATE() 
) 
 
create table [order_details]
(
[orderID] INT NOT NULL  ,
[line_item] INT NOT NULL ,
[ProductID] INT NULL ,
[Qtu] int NOT NULL,
[prise] money NOT NULL ,
[total_prise] AS CONVERT ( money, Qtu*prise)
) 
 
--КЛЮЧИ
ALTER TABLE [dbo].[customers]
ADD
PRIMARY KEY (ID)
go
 
ALTER TABLE [dbo].[products]
ADD
PRIMARY KEY (ID)
go
 
 
ALTER TABLE [dbo].[products_details]
ADD
UNIQUE(ID)
go
 
ALTER TABLE [dbo].[products_details]
ADD
   FOREIGN KEY (ID) REFERENCES [dbo].[products](ID)
     ON DELETE CASCADE 
go
 
ALTER TABLE [dbo].[stock]
ADD
UNIQUE([ProductID])
go
 
ALTER TABLE [dbo].[stock]
ADD
   FOREIGN KEY ([ProductID]) REFERENCES [dbo].[products](ID)
     ON DELETE CASCADE 
go
 
ALTER TABLE [dbo].[order]
ADD
PRIMARY KEY (ID)
go
 
ALTER TABLE [dbo].[order]
ADD
   FOREIGN KEY ([customersID]) REFERENCES [dbo].[customers](ID)
     ON DELETE SET NULL
go
 
ALTER TABLE [dbo].[order_details]
ADD
PRIMARY KEY ([orderID],[line_item])
go
 
 
ALTER TABLE [dbo].[order_details]
ADD
   FOREIGN KEY ([orderID]) REFERENCES [dbo].[order](ID)
                                                            --НЕТ УДАЛЕНИЯ ЗКАЗА 
go
 
ALTER TABLE [dbo].[order_details]
ADD
   FOREIGN KEY ([ProductID]) REFERENCES [dbo].[products](ID)
     ON DELETE SET NULL
go
 
--ПОЛЬЗОВАТЕЛЬСКИЕ ОГРАНИЧЕНИЯ 
 
ALTER TABLE [dbo].[customers]
ADD
CHECK  ([Phone] LIKE '([0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9])') 
GO
 
ALTER TABLE [dbo].[order]
ADD
CHECK  ([OrderDate] >= DATEADD(DAY ,- 900, GETDATE()) AND [OrderDate] <= GETDATE())
GO
 
ALTER TABLE [dbo].[stock]
ADD
CHECK  ([Name_Edinitsy] IN ('л','м','кг','шт','ед'))
GO
 
ALTER TABLE [dbo].[customers]
ADD
CHECK  ([DateInSystem] >= DATEADD(DAY ,- 900, GETDATE()) AND [DateInSystem] <= GETDATE())
GO
 
ALTER TABLE [dbo].[stock]
ADD
CHECK  ([Qtu] >=0 )
GO
 
 
--ТРИГГЕРЫ  
 
CREATE TRIGGER уменьшение_склада_при_заказе   
ON [dbo].[order_details]
FOR INSERT
AS
  IF @@ROWCOUNT = 0 
      RETURN 
      SET NOCOUNT ON 
 
UPDATE [dbo].[stock]
SET Qtu = s.Qtu - i.Qtu
FROM [dbo].[stock] s JOIN 
(SELECT [ProductID], SUM (Qtu) Qtu FROM inserted  GROUP BY [ProductID] ) i 
ON s.[ProductID] = i.[ProductID]
go
 
 
CREATE TRIGGER увеличение_склада_при_заказе   
ON [dbo].[order_details]
FOR DELETE
AS
  IF @@ROWCOUNT = 0 
      RETURN 
      SET NOCOUNT ON 
 
UPDATE [dbo].[stock]
SET Qtu = s.Qtu + d.Qtu
FROM [dbo].[stock] s JOIN 
(SELECT [ProductID], SUM (Qtu) Qtu FROM deleted  GROUP BY [ProductID] ) d 
ON s.[ProductID] = d.[ProductID]
go
 
 
CREATE TRIGGER изменение_заказа_и_склада   
ON [dbo].[order_details]
FOR UPDATE
AS
  IF @@ROWCOUNT = 0 
      RETURN 
  IF NOT UPDATE (Qtu) 
      RETURN
      SET NOCOUNT ON 
 
UPDATE [dbo].[stock]
SET Qtu = s.Qtu - (i.Qtu - d.Qtu)
FROM [dbo].[stock] s JOIN 
(SELECT [ProductID], SUM (Qtu) Qtu FROM deleted  GROUP BY [ProductID] ) d 
ON s.[ProductID] = d.[ProductID]
JOIN 
(SELECT [ProductID], SUM (Qtu) Qtu FROM inserted  GROUP BY [ProductID] ) i 
ON s.[ProductID] = i.[ProductID]
go
 
 
CREATE TRIGGER вз_удал_тавара_при_ост0_и_нет_в_заказах
ON [dbo].[products]
INSTEAD OF DELETE
AS
  IF @@ROWCOUNT = 0 
      RETURN 
      SET NOCOUNT ON 
  IF EXISTS (SELECT 1 FROM [dbo].[order_details] od
                      JOIN deleted d
                      ON od.ProductID = d.ID)
      RAISERROR ('Тавар не может быть удален т.к состоит в заказах', 10,1)
 
  ELSE IF EXISTS (SELECT 1 FROM [dbo].[stock] s
                      JOIN deleted d
                      ON s.ProductID = d.ID
                      WHERE s.Qtu <> 0 )
      RAISERROR ('Тавар не может быть удален т.к есть остаток на складе ', 10,2)
  ELSE 
      DELETE [dbo].[products] WHERE ID IN (SELECT ID FROM deleted)
 
go