---------------
-- Задание 1 --
---------------

CREATE TRIGGER ChangeDriverOnDelivery ON Deliveries
AFTER INSERT
AS BEGIN
    DECLARE @driver_id INT;
    SELECT @driver_id=driver_id FROM inserted;

    UPDATE Drivers
    SET on_delivery=1
    WHERE id=@driver_id;
END
GO

DROP TRIGGER ChangeDriverOnDelivery
GO

CREATE TRIGGER UpdateDriverOnDelivery ON Deliveries
AFTER UPDATE
AS BEGIN
    DECLARE @old_driver_id INT, @new_driver_id INT;
    SELECT @old_driver_id=driver_id FROM deleted;
    SELECT @new_driver_id=driver_id FROM inserted;

    UPDATE Drivers
    SET on_delivery=0
    WHERE id=@old_driver_id;
    UPDATE Drivers
    SET on_delivery=1
    WHERE id=@new_driver_id;
END
GO

DROP TRIGGER UpdateDriverOnDelivery
GO

CREATE TRIGGER RemoveDriverOnDelivery ON Deliveries
AFTER DELETE
AS BEGIN
    DECLARE @driver_id INT;
    SELECT @driver_id=driver_id FROM deleted;

    UPDATE Drivers
    SET on_delivery=0
    WHERE id=@driver_id;
END
GO

DROP TRIGGER RemoveDriverOnDelivery
GO

INSERT INTO Deliveries (driver_id, vehicle_id, route_id, date)
VALUES (1, 1, 1, '2021-04-15')
GO

UPDATE Deliveries
SET driver_id=2
WHERE driver_id=1
GO

DELETE FROM Deliveries WHERE driver_id=2
GO

---------------
-- Задание 2 --
---------------

CREATE VIEW OrdersView WITH SCHEMABINDING AS
SELECT Orders.id, Orders.customer_id,
       Orders.date order_date,
       D.driver_id, D.vehicle_id, D.route_id,
       D.date delivery_date
FROM dbo.Orders
JOIN dbo.Shipments ON Orders.id = Shipments.order_id
JOIN dbo.Deliveries D on Shipments.delivery_id = D.id
GO

DROP VIEW OrdersView
GO

-- INSTEAD OF INSERT

CREATE TRIGGER InsertOrdersView ON OrdersView
INSTEAD OF INSERT
AS BEGIN
    DECLARE @route_id INT, @city_src INT, @city_dst INT;
    SELECT @route_id=inserted.route_id FROM inserted;
    SELECT @city_src=city1_id, @city_dst=city2_id FROM Routes WHERE id=@route_id;
    IF @city_src IS NULL OR @city_dst IS NULL BEGIN
        RAISERROR (N'Не найден указанный путь', 10, 1)
        ROLLBACK TRAN
    END

    INSERT INTO Orders (customer_id, city_id, date)
    SELECT inserted.customer_id, @city_dst, inserted.order_date
    FROM inserted;
    DECLARE @order_id INT;
    SET @order_id = SCOPE_IDENTITY();

    INSERT INTO Deliveries (driver_id, vehicle_id, route_id, date)
    SELECT inserted.driver_id, inserted.vehicle_id, inserted.route_id, inserted.delivery_date
    FROM inserted;
    DECLARE @delivery_id INT;
    SET @delivery_id = SCOPE_IDENTITY();

    INSERT INTO Shipments (order_id, delivery_id) VALUES (@order_id, @delivery_id);
END
GO

DROP TRIGGER InsertOrdersView
GO

INSERT INTO OrdersView (id, customer_id, order_date, driver_id, vehicle_id, route_id, delivery_date)
VALUES (1, 123, '2021-09-12', 2, 1, 1, '2021-09-15')
GO

-- INSTEAD OF UPDATE

CREATE TRIGGER UpdateOrdersView ON OrdersView
INSTEAD OF UPDATE
AS BEGIN
    DECLARE @route_id INT, @city_src INT, @city_dst INT;
    SELECT @route_id=route_id FROM inserted;
    SELECT @city_src=city1_id, @city_dst=city2_id FROM Routes WHERE id=@route_id;
    IF @city_src IS NULL OR @city_dst IS NULL BEGIN
        RAISERROR (N'Не найден указанный путь', 10, 1)
        ROLLBACK TRAN
    END

    UPDATE Orders
    SET customer_id=inserted.customer_id,
        city_id=@city_dst,
        date=inserted.order_date
    FROM inserted
    WHERE Orders.id=inserted.id;

    DECLARE @order_id INT, @delivery_id INT;
    SELECT @order_id=inserted.id FROM inserted;
    SELECT @delivery_id=delivery_id FROM Shipments WHERE order_id=@order_id;

    UPDATE Deliveries
    SET driver_id=inserted.driver_id,
        vehicle_id=inserted.vehicle_id,
        route_id=inserted.route_id,
        date=inserted.delivery_date
    FROM inserted
    WHERE Deliveries.id = @delivery_id;
END
GO

DROP TRIGGER UpdateOrdersView
GO

UPDATE OrdersView
SET driver_id=1
WHERE order_date='2021-09-12'
GO

-- INSTEAD OF DELETE

CREATE TRIGGER DeleteOrdersView ON OrdersView
INSTEAD OF DELETE
AS BEGIN
    DECLARE @order_id INT, @delivery_id INT;
    SELECT @order_id = deleted.id FROM deleted;
    SELECT @delivery_id=delivery_id FROM Shipments WHERE order_id=@order_id;

    DELETE FROM Boxes WHERE order_id=@order_id;
    DELETE FROM Payments WHERE order_id=@order_id;
    DELETE FROM Shipments WHERE order_id=@order_id AND delivery_id=@delivery_id;
    DELETE FROM Deliveries WHERE id=@delivery_id;
    DELETE FROM Orders WHERE id=@order_id;
END
GO

DROP TRIGGER DeleteOrdersView
GO

DELETE FROM OrdersView WHERE order_date='2021-09-12'
GO