1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
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
|
