diff options
| -rw-r--r-- | task6_triggers.sql | 15 |
1 files changed, 9 insertions, 6 deletions
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 |