Добавил первое задание шестой лабы.

1 files changed, 60 insertions, 9 deletions

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