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
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
|
---------------
-- Задание 1 --
---------------
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 --
---------------
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
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', 2, 1, 1, '2021-09-15')
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=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
UPDATE OrdersView
SET driver_id=2
WHERE order_date='2021-09-12'
GO
-- 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=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 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
DROP TRIGGER DeleteOrdersView
GO
DELETE FROM OrdersView WHERE order_date='2021-09-12'
GO
|
