Pastein: -- ============================================================ -- 1. БАЗОВЫЕ СПРАВОЧНИКИ -- ============================================================ CREATE TABLE Countries

Загрузка данных
-- ============================================================
-- 1. БАЗОВЫЕ СПРАВОЧНИКИ
-- ============================================================

CREATE TABLE Countries (
    ID          INT IDENTITY(1,1) PRIMARY KEY,
    CountryName NVARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Positions (
    ID           INT IDENTITY(1,1) PRIMARY KEY,
    PositionName NVARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE WeaponTypes (
    ID          INT IDENTITY(1,1) PRIMARY KEY,
    TypeName    NVARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Calibers (
    ID          INT IDENTITY(1,1) PRIMARY KEY,
    CaliberName NVARCHAR(20) NOT NULL UNIQUE
);

-- ============================================================
-- 2. СУЩНОСТИ 2-ГО УРОВНЯ
-- ============================================================

CREATE TABLE Manufacturers (
    ID                  INT IDENTITY(1,1) PRIMARY KEY,
    ManufacturerName    NVARCHAR(100) NOT NULL,
    CountryID           INT           NOT NULL,
    CONSTRAINT FK_Manufacturers_Country FOREIGN KEY (CountryID) REFERENCES Countries(ID)
);

CREATE TABLE Suppliers (
    ID              INT IDENTITY(1,1) PRIMARY KEY,
    SupplierName    NVARCHAR(100) NOT NULL,
    CountryID       INT           NOT NULL,
    ContactPhone    NVARCHAR(20)  NOT NULL,
    CONSTRAINT FK_Suppliers_Country FOREIGN KEY (CountryID) REFERENCES Countries(ID)
);

CREATE TABLE Employees (
    ID              INT IDENTITY(1,1) PRIMARY KEY,
    FirstName       NVARCHAR(50)  NOT NULL,
    LastName        NVARCHAR(50)  NOT NULL,
    PositionID      INT           NOT NULL,
    EmployeeNumber  NVARCHAR(20)  NOT NULL UNIQUE,
    HireDate        DATE          NOT NULL,
    CONSTRAINT FK_Employees_Position FOREIGN KEY (PositionID) REFERENCES Positions(ID)
);

-- ============================================================
-- 3. ОСНОВНЫЕ СУЩНОСТИ И СКЛАД
-- ============================================================

CREATE TABLE Weapons (
    ID              INT IDENTITY(1,1) PRIMARY KEY,
    ModelName       NVARCHAR(100)   NOT NULL,
    TypeID          INT             NOT NULL,
    ManufacturerID  INT             NOT NULL,
    CaliberID       INT             NOT NULL,
    CONSTRAINT FK_Weapons_Type         FOREIGN KEY (TypeID)         REFERENCES WeaponTypes(ID),
    CONSTRAINT FK_Weapons_Manufacturer FOREIGN KEY (ManufacturerID) REFERENCES Manufacturers(ID),
    CONSTRAINT FK_Weapons_Caliber      FOREIGN KEY (CaliberID)      REFERENCES Calibers(ID)
);

-- ИСПРАВЛЕНИЕ 1: добавлен суррогатный первичный ключ ID,
-- WeaponID сохранён как UNIQUE NOT NULL (гарантирует 1:1 с Weapons)
CREATE TABLE Stock (
    ID       INT IDENTITY(1,1) PRIMARY KEY,        -- добавлен ID
    WeaponID INT NOT NULL UNIQUE,                   -- уникальность сохранена
    Quantity INT NOT NULL DEFAULT 0 CHECK (Quantity >= 0),
    CONSTRAINT FK_Stock_Weapon FOREIGN KEY (WeaponID) REFERENCES Weapons(ID)
);

CREATE TABLE PriceHistory (
    ID              INT IDENTITY(1,1) PRIMARY KEY,
    WeaponID        INT             NOT NULL,
    Price           DECIMAL(10,2)   NOT NULL CHECK (Price > 0),
    EffectiveDate   DATE            NOT NULL,
    CONSTRAINT FK_PriceHistory_Weapon FOREIGN KEY (WeaponID) REFERENCES Weapons(ID)
);

-- Вьюшка для получения актуальной цены (последняя запись по дате)
GO
CREATE VIEW vw_CurrentPrice AS
    SELECT w.ID AS WeaponID, w.ModelName, ph.Price, ph.EffectiveDate
    FROM Weapons w
    CROSS APPLY (
        SELECT TOP 1 Price, EffectiveDate
        FROM PriceHistory
        WHERE WeaponID = w.ID
        ORDER BY EffectiveDate DESC
    ) ph;
GO

CREATE TABLE Clients (
    ID              INT IDENTITY(1,1) PRIMARY KEY,
    FirstName       NVARCHAR(50)  NOT NULL,
    LastName        NVARCHAR(50)  NOT NULL,
    PassportNumber  NVARCHAR(20)  NOT NULL UNIQUE,
    Phone           NVARCHAR(20)  NOT NULL
);

CREATE TABLE Licenses (
    ID              INT IDENTITY(1,1) PRIMARY KEY,
    ClientID        INT             NOT NULL,
    LicenseNumber   NVARCHAR(30)    NOT NULL UNIQUE,
    IssueDate       DATE            NOT NULL,
    ExpiryDate      DATE            NOT NULL,
    CONSTRAINT FK_Licenses_Client FOREIGN KEY (ClientID) REFERENCES Clients(ID),
    CONSTRAINT CHK_LicenseDates CHECK (ExpiryDate > IssueDate)
);

-- ============================================================
-- 4. ОПЕРАЦИИ: ПОСТАВКИ
-- ============================================================

CREATE TABLE Deliveries (
    ID                  INT IDENTITY(1,1) PRIMARY KEY,
    SupplierID          INT             NOT NULL,
    EmployeeID          INT             NOT NULL,
    DeliveryDate        DATE            NOT NULL,
    CONSTRAINT FK_Deliveries_Supplier FOREIGN KEY (SupplierID) REFERENCES Suppliers(ID),
    CONSTRAINT FK_Deliveries_Employee FOREIGN KEY (EmployeeID) REFERENCES Employees(ID)
);

CREATE TABLE DeliveryDetails (
    ID                  INT IDENTITY(1,1) PRIMARY KEY,
    DeliveryID          INT             NOT NULL,
    WeaponID            INT             NOT NULL,
    Quantity            INT             NOT NULL CHECK (Quantity > 0),
    UnitPrice           DECIMAL(10,2)   NOT NULL CHECK (UnitPrice > 0),
    CONSTRAINT FK_DeliveryDetails_Delivery FOREIGN KEY (DeliveryID) REFERENCES Deliveries(ID) ON DELETE CASCADE,
    CONSTRAINT FK_DeliveryDetails_Weapon   FOREIGN KEY (WeaponID)   REFERENCES Weapons(ID)
);

-- ============================================================
-- 5. ОПЕРАЦИИ: ПРОДАЖИ
-- ============================================================

-- ИСПРАВЛЕНИЕ 2: добавлен тип INT для EmployeeID (был синтаксическая ошибка)
CREATE TABLE Sales (
    ID          INT IDENTITY(1,1) PRIMARY KEY,
    ClientID    INT     NOT NULL,
    LicenseID   INT     NOT NULL,
    EmployeeID  INT     NOT NULL,    -- исправлено: добавлен тип данных INT
    SaleDate    DATE    NOT NULL,
    CONSTRAINT FK_Sales_Client   FOREIGN KEY (ClientID)   REFERENCES Clients(ID),
    CONSTRAINT FK_Sales_License  FOREIGN KEY (LicenseID)  REFERENCES Licenses(ID),
    CONSTRAINT FK_Sales_Employee FOREIGN KEY (EmployeeID) REFERENCES Employees(ID)
);

CREATE TABLE SaleDetails (
    ID          INT IDENTITY(1,1) PRIMARY KEY,
    SaleID      INT     NOT NULL,
    WeaponID    INT     NOT NULL,
    Quantity    INT     NOT NULL CHECK (Quantity > 0),
    SalePrice   DECIMAL(10,2) NOT NULL CHECK (SalePrice > 0),
    CONSTRAINT FK_SaleDetails_Sale   FOREIGN KEY (SaleID)   REFERENCES Sales(ID) ON DELETE CASCADE,
    CONSTRAINT FK_SaleDetails_Weapon FOREIGN KEY (WeaponID) REFERENCES Weapons(ID)
);
GO

-- ============================================================
-- ИНДЕКСЫ НА ВНЕШНИЕ КЛЮЧИ
-- (SQL Server не создаёт их автоматически, в отличие от MySQL)
-- ============================================================

CREATE INDEX IX_Manufacturers_CountryID    ON Manufacturers(CountryID);
CREATE INDEX IX_Suppliers_CountryID        ON Suppliers(CountryID);
CREATE INDEX IX_Employees_PositionID       ON Employees(PositionID);
CREATE INDEX IX_Weapons_TypeID             ON Weapons(TypeID);
CREATE INDEX IX_Weapons_ManufacturerID     ON Weapons(ManufacturerID);
CREATE INDEX IX_Weapons_CaliberID          ON Weapons(CaliberID);
CREATE INDEX IX_PriceHistory_WeaponID      ON PriceHistory(WeaponID);
CREATE INDEX IX_Licenses_ClientID          ON Licenses(ClientID);
CREATE INDEX IX_Deliveries_SupplierID      ON Deliveries(SupplierID);
CREATE INDEX IX_Deliveries_EmployeeID      ON Deliveries(EmployeeID);
CREATE INDEX IX_DeliveryDetails_DeliveryID ON DeliveryDetails(DeliveryID);
CREATE INDEX IX_DeliveryDetails_WeaponID   ON DeliveryDetails(WeaponID);
CREATE INDEX IX_Sales_ClientID             ON Sales(ClientID);
CREATE INDEX IX_Sales_LicenseID            ON Sales(LicenseID);
CREATE INDEX IX_Sales_EmployeeID           ON Sales(EmployeeID);
CREATE INDEX IX_SaleDetails_SaleID         ON SaleDetails(SaleID);
CREATE INDEX IX_SaleDetails_WeaponID       ON SaleDetails(WeaponID);
GO

-- ============================================================
-- ТРИГГЕРЫ ДЛЯ АВТОМАТИЗАЦИИ СКЛАДА (STOCK)
-- ============================================================

-- Триггер 1: приход товара — увеличивает остаток в Stock
CREATE TRIGGER trg_DeliveryDetails_UpdateStock
ON DeliveryDetails
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- Обновляем существующие записи
    UPDATE s
    SET s.Quantity = s.Quantity + i.Quantity
    FROM Stock s
    JOIN inserted i ON s.WeaponID = i.WeaponID;

    -- Добавляем новые записи для оружия, которого ещё не было на складе
    INSERT INTO Stock (WeaponID, Quantity)
    SELECT i.WeaponID, i.Quantity
    FROM inserted i
    WHERE NOT EXISTS (SELECT 1 FROM Stock s WHERE s.WeaponID = i.WeaponID);
END;
GO

-- Триггер 2: продажа товара — проверяет наличие и уменьшает остаток
CREATE TRIGGER trg_SaleDetails_UpdateStock
ON SaleDetails
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    -- Проверка: достаточно ли товара на складе?
    IF EXISTS (
        SELECT 1
        FROM inserted i
        LEFT JOIN Stock s ON i.WeaponID = s.WeaponID
        WHERE ISNULL(s.Quantity, 0) < i.Quantity
    )
    BEGIN
        -- ИСПРАВЛЕНИЕ 3: THROW вместо устаревшего RAISERROR
        THROW 50001, N'Ошибка: недостаточно товара на складе для совершения продажи.', 1;
    END;

    -- Списываем количество
    UPDATE s
    SET s.Quantity = s.Quantity - i.Quantity
    FROM Stock s
    JOIN inserted i ON s.WeaponID = i.WeaponID;
END;
GO

-- ============================================================
-- ТРИГГЕР: ПРОВЕРКА ПРИНАДЛЕЖНОСТИ ЛИЦЕНЗИИ КЛИЕНТУ
-- ИСПРАВЛЕНИЕ 4: лицензия должна принадлежать клиенту из той же продажи
-- ============================================================

CREATE TRIGGER trg_Sales_CheckLicenseOwner
ON Sales
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (
        SELECT 1
        FROM inserted i
        JOIN Licenses l ON i.LicenseID = l.ID
        WHERE l.ClientID <> i.ClientID
    )
    BEGIN
        THROW 50002, N'Ошибка: лицензия не принадлежит указанному клиенту.', 1;
    END;
END;
GO

-- ============================================================
-- ТРИГГЕР: ПРОВЕРКА СРОКА ДЕЙСТВИЯ ЛИЦЕНЗИИ ПРИ ПРОДАЖЕ
-- ИСПРАВЛЕНИЕ 5: нельзя продавать по просроченной лицензии
-- ============================================================

CREATE TRIGGER trg_Sales_CheckLicenseExpiry
ON Sales
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    IF EXISTS (
        SELECT 1
        FROM inserted i
        JOIN Licenses l ON i.LicenseID = l.ID
        WHERE l.ExpiryDate < i.SaleDate
    )
    BEGIN
        THROW 50003, N'Ошибка: срок действия лицензии истёк.', 1;
    END;
END;
GO

-- ============================================================
-- ЗАПОЛНЕНИЕ ТЕСТОВЫМИ ДАННЫМИ
-- ============================================================

-- Справочники
INSERT INTO Countries (CountryName) VALUES ('Россия'), ('Австрия'), ('США'), ('Италия'), ('Германия');
INSERT INTO Positions (PositionName) VALUES ('Продавец-консультант'), ('Кладовщик');
INSERT INTO WeaponTypes (TypeName) VALUES ('Пистолет'), ('Винтовка'), ('Автомат');
INSERT INTO Calibers (CaliberName) VALUES ('5.45x39'), ('9x19'), ('12/76');

-- Производители
INSERT INTO Manufacturers (ManufacturerName, CountryID) VALUES 
('Kalashnikov Concern', 1), 
('Glock GmbH', 2);

-- Поставщики и сотрудники
INSERT INTO Suppliers (SupplierName, CountryID, ContactPhone) VALUES ('АрмТорг', 1, '+7-495-111');
INSERT INTO Employees (FirstName, LastName, PositionID, EmployeeNumber, HireDate) 
VALUES ('Иван', 'Петров', 1, 'EMP-001', '2020-03-15');

-- Оружие
INSERT INTO Weapons (ModelName, TypeID, ManufacturerID, CaliberID) VALUES 
('АК-74', 3, 1, 1), 
('Glock 17', 1, 2, 2);

-- История цен
INSERT INTO PriceHistory (WeaponID, Price, EffectiveDate) VALUES
(1, 30000.00, '2023-01-01'),
(1, 32000.00, '2024-01-01'),
(2, 22000.00, '2023-01-01'),
(2, 25000.00, '2024-01-01');

-- Клиенты
INSERT INTO Clients (FirstName, LastName, PassportNumber, Phone) 
VALUES ('Андрей', 'Волков', 'MD123456', '+373-69');

INSERT INTO Licenses (ClientID, LicenseNumber, IssueDate, ExpiryDate) 
VALUES (1, 'ЛИЦ-001', '2023-01-15', '2026-01-15');

-- Поставка (триггер автоматически обновит Stock)
INSERT INTO Deliveries (SupplierID, EmployeeID, DeliveryDate) VALUES (1, 1, '2024-01-10');
INSERT INTO DeliveryDetails (DeliveryID, WeaponID, Quantity, UnitPrice) VALUES 
(1, 1, 10, 32000.00),  -- 10 АК-74
(1, 2, 5,  25000.00);  -- 5 Glock 17

-- Продажа (триггер проверит остаток, принадлежность лицензии и её срок)
INSERT INTO Sales (ClientID, LicenseID, EmployeeID, SaleDate) VALUES (1, 1, 1, '2024-02-01');
INSERT INTO SaleDetails (SaleID, WeaponID, Quantity, SalePrice) VALUES 
(1, 1, 2, 45000.00);   -- 2 АК-74 (останется 8)

-- ============================================================
-- ПРОВЕРОЧНЫЕ ЗАПРОСЫ
-- ============================================================

-- Текущие остатки на складе
SELECT w.ModelName, s.Quantity
FROM Stock s
JOIN Weapons w ON s.WeaponID = w.ID;

-- Актуальные цены (через вьюшку)
SELECT * FROM vw_CurrentPrice;

-- Продажи с данными клиента и лицензии
SELECT 
    sa.ID AS SaleID,
    sa.SaleDate,
    c.FirstName + ' ' + c.LastName AS Client,
    l.LicenseNumber,
    l.ExpiryDate,
    w.ModelName,
    sd.Quantity,
    sd.SalePrice
FROM Sales sa
JOIN Clients c    ON sa.ClientID  = c.ID
JOIN Licenses l   ON sa.LicenseID = l.ID
JOIN SaleDetails sd ON sa.ID      = sd.SaleID
JOIN Weapons w    ON sd.WeaponID  = w.ID;
Больше возможностей при регистрации:
