HP-Triggers

HP-Triggers.sql

--STORED PROCEDURES (выполнить минимум 3 задания):
--3. Написать хранимую процедуру, которая показывает список производителей и общее количество товаров, произведённых каждым из них.
--CREATE PROCEDURE ProducersAndProducts
--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
--	ORDER BY "общее количество товаров" DESC
--END
--EXEC ProducersAndProducts

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

--5. Создать хранимую процедуру, которая возвращает общее количество проданных товаров и общего дохода.
--CREATE PROCEDURE TotalSaleIncome
--AS
--BEGIN
--	SELECT
--		SUM(s.quantity) AS "общее количество проданных товаров",
--		SUM(s.quantity * s.price) AS "общий доход"
--	FROM Sale s
--END
--EXEC TotalSaleIncome

--TRIGGERS (выполнить минимум 3 задания):
--1. При продаже товара (добавлении записи в таблицу Sale), его количество должно уменьшаться (в таблице Product).
--CREATE TRIGGER ProductQuantity
--ON Sale
--AFTER INSERT
--AS
--BEGIN
--    IF EXISTS(
--        SELECT *
--        FROM Product p
--        JOIN Inserted i ON i.id_product = p.id 
--        WHERE p.quantity - i.quantity < 0)
--    BEGIN
--        PRINT 'hello from trigger'
--        ROLLBACK TRAN
--    END
--    UPDATE p
--    SET p.quantity -= i.quantity
--    FROM Product p
--    JOIN inserted i ON i.id_product = p.id
--END

--2. Нельзя продать товар, которого нет на складе магазина (по количеству).
--CREATE TRIGGER ProductForSale
--ON Sale
--AFTER INSERT
--AS
--BEGIN
--	IF EXISTS(
--		SELECT *
--		FROM Product p
--		JOIN Inserted i ON i.id_product = p.id
--		WHERE p.quantity < i.quantity)
--	BEGIN
--		PRINT 'hello from trigger'
--		ROLLBACK TRAN
--	END
--END

--3. Нельзя продать больше 100 единиц одного товара одному покупателю.
--CREATE TRIGGER CheckMaxSale
--ON Sale
--AFTER INSERT
--AS
--BEGIN
--	IF EXISTS(
--		SELECT *
--		FROM Inserted i
--		WHERE i.quantity > 100)
--	BEGIN
--		PRINT 'hello from trigger'
--		ROLLBACK TRAN
--	END
--END