From 6d09750baab76fd5892201fb723357153cc4c442 Mon Sep 17 00:00:00 2001
From: Andrew <saintruler@gmail.com>
Date: Fri, 17 Dec 2021 01:30:04 +0400
Subject: =?UTF-8?q?=D0=94=D0=BE=D0=B1=D0=B0=D0=B2=D0=B8=D0=BB=20=D0=BF?=
 =?UTF-8?q?=D0=B5=D1=80=D0=B2=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 | 69 +++++++++++++++++++++++++++++++++++++++++++++++-------
 1 file changed, 60 insertions(+), 9 deletions(-)

(limited to 'task6_triggers.sql')

diff --git a/task6_triggers.sql b/task6_triggers.sql
index 3c8a147..1c1a5f6 100644
--- a/task6_triggers.sql
+++ b/task6_triggers.sql
@@ -2,14 +2,66 @@
 -- Задание 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
+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-09-08')
+GO
+
+UPDATE Deliveries
+SET driver_id=2
+WHERE driver_id=1
+GO
+
+DELETE FROM Deliveries WHERE driver_id=2
+GO
 
 ---------------
 -- Задание 2 --
@@ -23,7 +75,6 @@ SELECT Orders.id, Orders.customer_id,
 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
-- 
cgit v1.2.3