From 8cb863786cd06ea8c44bea35b77002998075d3bb Mon Sep 17 00:00:00 2001
From: Andrew <saintruler@gmail.com>
Date: Thu, 16 Dec 2021 22:07:07 +0400
Subject: =?UTF-8?q?=D0=94=D0=BE=D0=B1=D0=B0=D0=B2=D0=B8=D0=BB=20=D0=B2?=
 =?UTF-8?q?=D1=82=D0=BE=D1=80=D0=BE=D0=B5=20=D0=B7=D0=B0=D0=B4=D0=B0=D0=BD?=
 =?UTF-8?q?=D0=B8=D0=B5=20=D1=88=D0=B5=D1=81=D1=82=D0=BE=D0=B9=20=D0=BB?=
 =?UTF-8?q?=D0=B0=D0=B1=D1=8B.?=
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

---
 task6_triggers.sql | 128 +++++++++++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 128 insertions(+)
 create mode 100644 task6_triggers.sql

diff --git a/task6_triggers.sql b/task6_triggers.sql
new file mode 100644
index 0000000..3c8a147
--- /dev/null
+++ b/task6_triggers.sql
@@ -0,0 +1,128 @@
+---------------
+-- Задание 1 --
+---------------
+
+-- CREATE TRIGGER OrderDateUpdate ON Orders
+-- AFTER UPDATE
+-- AS BEGIN
+--     DECLARE @order_id INT, @delivery_id INT, @date DATETIME;
+--     SELECT @order_id = inserted.id FROM inserted;
+--     SELECT @delivery_id FROM Shipments WHERE order_id=@order_id;
+--     UPDATE Deliveries SET date=@date WHERE id=@delivery_id;
+-- END
+
+---------------
+-- Задание 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
+JOIN dbo.Cities ON Orders.city_id = Cities.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', 43, 12, 6, '2021-09-24')
+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 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
+
+-- TODO: Добавить запрос с UPDATE
+
+-- 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 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 Deliveries WHERE id=@delivery_id;
+    DELETE FROM Shipments WHERE order_id=@order_id AND delivery_id=@delivery_id;
+    DELETE FROM Orders WHERE id=@order_id;
+END
+GO
+
+DROP TRIGGER DeleteOrdersView
+GO
+
+DELETE FROM OrdersView WHERE delivery_date='2021-09-12'
+GO
-- 
cgit v1.2.3