procedures + triggers

hw_proc_trigg.txt

USE StoreNew

-- 1. Написать хранимую процедуру, которая показывает общее количество проданных товаров в каждой из категорий и от каждого производителя.
CREATE PROCEDURE SaledProd AS
SELECT 
	p.name AS product_name, 
	c.name AS category_name,
	SUM(s.quantity) AS total_sales,
	pr.name AS producer_name
FROM Product p
JOIN Category c ON p.id_category = c.id
JOIN Producer pr ON p.id_producer = pr.id
JOIN Sale s ON s.id_product = p.id
GROUP BY p.name, c.name, pr.name;

EXEC SaledProd

-- 2. Написать хранимую процедуру, которая показывает список производителей и общее количество товаров, произведённых каждым из них.
CREATE PROCEDURE ProdCount AS
BEGIN
  SELECT 
        pr.name AS producer_name,
        COUNT(p.id) AS product_count
    FROM Product p
    JOIN Producer pr ON p.id_producer = pr.id
    GROUP BY pr.name;
END

EXEC ProdCount

-- 3. Создать хранимую процедуру, которая показывает название продукта, который был продан в максимальном количестве.
CREATE PROCEDURE BestSellProd AS
BEGIN
    SELECT TOP 1
        p.name AS product_name,
        SUM(s.quantity) AS total_sales
    FROM Product p
    JOIN Sale s ON s.id_product = p.id
    GROUP BY p.name
    ORDER BY SUM(s.quantity) DESC
END

EXEC BestSellProd

-- 4. При удалении товара из таблицы Product, данные о нём должны копироваться в резервную таблицу ProductArchive.
CREATE TRIGGER trg1
ON Product AFTER DELETE AS
BEGIN
    INSERT INTO ProductArchive
    SELECT * FROM deleted;
END

-- 5. При продаже товара (добавлении записи в таблицу Sale), его количество должно уменьшаться (в таблице Product).
CREATE TRIGGER trg2
ON Sale AFTER INSERT AS
BEGIN
    UPDATE Product 
    SET Product.quantity = Product.quantity - INSERTED.quantity
    FROM Product
    JOIN INSERTED ON Product.id = inserted.id_product
END

-- 6. Нельзя продать товар, которого нет на складе магазина (по количеству).
CREATE TRIGGER trg3 ON Sale
AFTER INSERT
AS
IF EXISTS (
        SELECT 1 
        FROM INSERTED i
        JOIN Product p ON i.id_product = p.id
        WHERE p.quantity < i.quantity
    ) 
ROLLBACK TRAN