From 9bd4a687c64c31dbed810e65297601d0233242f2 Mon Sep 17 00:00:00 2001 From: Andrew Date: Fri, 17 Dec 2021 08:05:43 +0400 Subject: =?UTF-8?q?=D0=98=D1=81=D0=BF=D1=80=D0=B0=D0=B2=D0=B8=D0=BB=20?= =?UTF-8?q?=D1=88=D0=B5=D1=81=D1=82=D1=83=D1=8E=20=D0=BB=D0=B0=D0=B1=D1=83?= MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit --- task6_triggers.sql | 15 +++++++++------ 1 file changed, 9 insertions(+), 6 deletions(-) (limited to 'task6_triggers.sql') diff --git a/task6_triggers.sql b/task6_triggers.sql index c6519d8..67c79e3 100644 --- a/task6_triggers.sql +++ b/task6_triggers.sql @@ -131,7 +131,7 @@ 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') +VALUES (1, 123, '2021-09-12', 2, 1, 1, '2021-09-15') GO -- INSTEAD OF UPDATE @@ -156,7 +156,7 @@ AS BEGIN DECLARE @order_id INT, @delivery_id INT; SELECT @order_id=inserted.id FROM inserted; - SELECT @delivery_id FROM Shipments WHERE order_id=@order_id; + SELECT @delivery_id=delivery_id FROM Shipments WHERE order_id=@order_id; UPDATE Deliveries SET driver_id=inserted.driver_id, @@ -171,7 +171,10 @@ GO DROP TRIGGER UpdateOrdersView GO --- TODO: Добавить запрос с UPDATE +UPDATE OrdersView +SET driver_id=2 +WHERE order_date='2021-09-12' +GO -- INSTEAD OF DELETE @@ -180,12 +183,12 @@ 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; + 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 Deliveries WHERE id=@delivery_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 @@ -193,5 +196,5 @@ GO DROP TRIGGER DeleteOrdersView GO -DELETE FROM OrdersView WHERE delivery_date='2021-09-12' +DELETE FROM OrdersView WHERE order_date='2021-09-12' GO -- cgit v1.2.3