CREATE TRIGGER cancelledOrder
ON
Order_status
FOR UPDATE
AS BEGIN
IF (SELECT Status_description FROM INSERTED) = 'Cancelled'
BEGIN
DELETE FROM Order_items WHERE Order_id = (SELECT Order_id FROM INSERTED)
END
END



CREATE TRIGGER newOrder
ON
Orders
FOR INSERT
AS BEGIN
DECLARE @newOrderID INT, @newStatusID INT
SELECT @newOrderID = Order_id FROM INSERTED
SELECT @newStatusID = max(Status_id) + 1 FROM Order_status
INSERT INTO Order_status VALUES
(@newStatusID, 'Processing', @newOrderID, GETDATE())
END


CREATE TRIGGER preventDeleteProduct
ON
Products
FOR DELETE
AS BEGIN
IF EXISTS(SELECT * FROM DELETED WHERE Product_type = (SELECT Product_type_id FROM Product_type WHERE Product_type_name = 'Meat'))
BEGIN
    ROLLBACK;
    THROW 50001, 'Cannot delete product of type chicken', 1;
END
END

--------------------------------------------------------------------------------------
NEW TRIGGERS

-------------------------------------------------------
CREATE TRIGGER deliveredOrder
ON
Delivery_status
FOR UPDATE
AS
BEGIN
DECLARE @orderSum INT, @orderID INT, @custName VARCHAR(50)
IF (SELECT Delivery_status_description FROM INSERTED) = 'Delivered'
BEGIN
SELECT @orderID = order_id FROM Deliveries WHERE Delivery_id = (SELECT Delivery_id FROM INSERTED)
EXEC calculatingSumOut @orderID, @price = @orderSum OUTPUT
SELECT @custName = customer_lastname FROM Customers WHERE Customer_id = (SELECT Order_customer FROM Orders WHERE Order_id = @orderID)
PRINT cast(@custName as VARCHAR) + ' order was delivered for the amount of : ' + cast(@orderSum as VARCHAR)
END
END

-------------------------------------------------------------

CREATE TRIGGER discountCustomer
ON
Orders
FOR INSERT
AS BEGIN
DECLARE @deliveredOrders INT, @custID INT
SELECT @custID = Order_customer FROM INSERTED
EXEC @deliveredOrders = numberOfCompletedOrders @custID
IF @deliveredOrders > 10 AND @deliveredOrders < 20
BEGIN
    IF (SELECT COUNT(Customer_id) FROM Customer_discount WHERE Customer_id = @custID) < 0
    BEGIN
    INSERT INTO Customer_discount VALUES (@custID, 10)
    END
END
IF @deliveredOrders > 20
BEGIN
    IF (SELECT COUNT(Customer_id) FROM Customer_discount WHERE Customer_id = @custID) > 0
    BEGIN
    UPDATE Customer_discount SET Discount_percent = 20 WHERE Customer_id = @custID
    END
END
END