ДЗ Триггеры и ХП

ДЗ Триггеры и ХП.txt

STORED PROCEDURES (выполнить минимум 3 задания):
1.	Написать хранимую процедуру, которая показывает общее количество проданных товаров в каждой из категорий и от каждого производителя.

--1.	Написать хранимую процедуру, которая показывает общее количество проданных товаров в каждой из категорий и от каждого производителя.
CREATE PROCEDURE Products_Saled AS
    SELECT c.name AS [Категория], prr.name AS [Производитель], COUNT(1) AS [Количество проданных товаров]
    FROM Product p
    JOIN Category c ON p.id_category = c.id
    JOIN Sale s ON s.id_product = p.id
    JOIN Producer prr ON p.id_producer = prr.id
    GROUP BY c.name, prr.name
	ORDER BY 1,2
	
	EXEC Products_Saled


2.	Написать хранимую процедуру, которая показывает список товаров, отвечающих набору критериев. Критерии: название продукта, категория, производитель. Кроме того, список должен быть отсортирован по номеру поля, указанному в 4-м параметре, в направлении, указанном в 5-м параметре (пригодится sp_executesql).

CREATE PROCEDURE List_products
@ProductName nvarchar(50), @CategoryName nvarchar(50), @ProducerName nvarchar(50), @OrderColumn int, @OrderDirection int
AS
BEGIN
DECLARE @SQL nvarchar (MAX)
DECLARE @Params nvarchar(MAX) = N'@ProductName nvarchar(50), @CategoryName nvarchar(50), @ProducerName nvarchar(50),@OrderColumn int, @OrderDirection int'
SET @SQL = N'
SELECT p.name AS [Наименование продукта], c.name AS [Категория продукта], prr.name AS [Производитель]
FROM Product p
JOIN Category c ON p.id_category = c.id
JOIN Producer prr ON p.id_producer = prr.id
WHERE p.name LIKE N''%'' + @ProductName + N''%'' 
OR c.name LIKE N''%'' + @CategoryName + N''%''
OR prr.name LIKE N''%'' + @ProducerName + N''%''
ORDER BY ' + 
CASE 
    WHEN @OrderColumn = 1 THEN 'p.name' 
    WHEN @OrderColumn = 2 THEN 'c.name' 
    WHEN @OrderColumn = 3 THEN 'prr.name' 
    ELSE 'p.name' 
END
	+ ' ' + 
CASE @OrderDirection
    WHEN 1 THEN 'DESC' 
    ELSE 'ASC' 
END

EXEC sp_executesql @SQL, @Params, @ProductName, @CategoryName, @ProducerName, @OrderColumn, @OrderDirection
END
--DROP PROCEDURE List_products

EXEC List_products 'Кока-кола','Напої','Coca-Cola Company', 1 , 1

3.	Написать хранимую процедуру, которая показывает список производителей и общее количество товаров, произведённых каждым из них.


--CREATE PROCEDURE List_products
--AS
SELECT prr.name AS [производители], SUM (s.quantity+p.quantity+d.quantity) AS [общее количество]
FROM Producer prr
JOIN Product p ON p.id_producer = prr.id
LEFT JOIN Sale s ON s.id_product = p.id
FULL JOIN Delivery d ON d.id_product = p.id
GROUP BY prr.name


TRIGGERS (выполнить минимум 3 задания):
1.	При продаже товара (добавлении записи в таблицу Sale), его количество должно уменьшаться (в таблице Product).
=========================== Вариант с 1 INSERT =========================== 
CREATE TRIGGER tr1 ON Sale
AFTER INSERT
AS
BEGIN
PRINT 'hello from trigger!'
DECLARE @id_p int
SELECT @id_p = id_product FROM INSERTED

UPDATE Product
SET quantity = quantity - 1 
WHERE id = @id_p
END

=========================== Вариант если INSERT несколько ===========================
CREATE TRIGGER tr2 ON Sale
AFTER INSERT
AS
BEGIN
PRINT 'hello from trigger!'
UPDATE Product
    SET quantity = p.quantity - i.quantity
    FROM Product p
    JOIN INSERTED i ON p.id = i.id_product
END


2.	Нельзя продать товар, которого нет на складе магазина (по количеству).

CREATE TRIGGER tr3 ON Sale
AFTER INSERT
AS
DECLARE @id_p int
SELECT @id_p = id_product FROM INSERTED
IF EXISTS (
        SELECT 1 
        FROM INSERTED i
        JOIN Product p ON i.id_product = p.id
        WHERE p.quantity < i.quantity
    ) 
ROLLBACK TRAN

3.	Нельзя продать больше 100 единиц одного товара одному покупателю.

ALTER TRIGGER tr5 ON Sale
AFTER INSERT
AS
 PRINT 'hello from trigger5!'
IF EXISTS (
        SELECT 1 
        FROM INSERTED i
		GROUP BY i.id_product, i.id_customer 
        HAVING SUM(i.quantity) > 100
    ) 
ROLLBACK TRAN