dz7

gistfile1.txt

-- 1
CREATE PROCEDURE ShowAll
AS
BEGIN
	SELECT s.quantity AS [Кол-во],
		c.name AS Категория,
		pr.name AS Производитель
	FROM Sale s
	JOIN Product p ON s.id_product = p.id
	JOIN Category c ON p.id_category = c.id
	JOIN Producer pr ON p.id_producer = pr.id
END

-- 3
CREATE PROCEDURE ShowAllProducers
AS
BEGIN
	SELECT pr.name AS Производитель,
		COUNT(p.id) AS [Кол-во продуктов]
	FROM Producer pr
	JOIN Product p ON p.id_producer = pr.id
	GROUP BY pr.name
END

-- 4
CREATE PROCEDURE ShowMAX
AS
BEGIN
	SELECT TOP 1 p.name AS Продукт,
		s.quantity AS [Кол-во продаж]
	FROM Sale s
	JOIN Product p ON s.id_product = p.id
	ORDER BY s.quantity DESC
END

============================ TRIGGERS

-- 1, 2
CREATE TRIGGER trg_SaleUpdate
ON Sale
AFTER INSERT
AS
BEGIN
    UPDATE Product
    SET quantity -= i.quantity
    FROM Product p
    JOIN inserted i ON p.id = i.id_product
    WHERE p.quantity >= i.quantity
END

-- 3
CREATE TRIGGER trg_LimitSaleQuantity
ON Sale
AFTER INSERT
AS
BEGIN
	IF EXISTS(
	SELECT 1
	FROM inserted i
	WHERE i.quantity > 100
	)
	BEGIN
		RAISERROR('Нельзя продать более 100 единиц товара одному покупателю.', 16, 1)
		ROLLBACK TRANSACTION
		RETURN
	END

	UPDATE p
	SET p.quantity = p.quantity - i.quantity
	FROM Product p
	JOIN inserted i ON p.id = i.id_product
END