1PMiZ4RT

1use DB1
2--Создает таблицу Факультетов
3GO
4DROP table dbo.Faculties
5GO
6create table dbo.Faculties
7(
8	 Faculty VARCHAR(10) PRIMARY KEY NOT NULL,
9	 Dean VARCHAR (50) NOT NULL
10);
11GO
12use DB2
13
14
15--Создаем таблицу студентов
16GO
17DROP TABLE dbo.Studs
18GO
19create table dbo.Studs
20(
21	 ID INT PRIMARY KEY NOT NULL,
22	 name VARCHAR (50) NOT NULL,
23	 Surname VARCHAR (50) NOT NULL,
24	 Faculty VARCHAR (10) NOT NULL
25);
26GO
27use DB1
28
29
30--Тригер на удаление фаультетов
31GO
32IF OBJECT_ID('trig_DELETE_Fac', 'TR') IS NOT NULL
33	DROP TRIGGER dbo.trig_DELETE_Fac
34GO
35CREATE TRIGGER trig_DELETE_Fac
36	ON dbo.Faculties
37	AFTER DELETE
38	AS
39	IF EXISTS (SELECT 1 FROM DB2.dbo.Studs WHERE Faculty IN (SELECT Faculty FROM DELETED))
40	BEGIN
41		THROW 51000, 'Cannot delete Faculty in Faculties. This Faculty has reference in Students', 1
42		ROLLBACK TRANSACTION
43	END
44GO
45
46
47--Тригер на обновление факультетов
48IF OBJECT_ID('trig_UPDATE_Fac', 'TR') IS NOT NULL
49	DROP TRIGGER dbo.trig_UPDATE_Fac
50GO
51CREATE TRIGGER trig_UPDATE_Fac
52	ON dbo.Faculties
53	AFTER UPDATE
54	AS
55	IF (UPDATE(Faculty))
56		BEGIN
57			IF EXISTS (SELECT 1 FROM DB2.dbo.Studs WHERE Faculty IN (SELECT Faculty FROM DELETED WHERE Faculty NOT IN (SELECT Faculty FROM inserted)))
58			BEGIN
59				THROW 51000, 'Cannot UPDATE Faculty in Faculties. This Faculty has reference in Students', 1
60				ROLLBACK TRANSACTION
61			END
62		END
63GO
64use DB2
65
66
67--Тригер обновления Студентов
68GO
69IF OBJECT_ID('trig_UPDATE_Stud', 'TR') IS NOT NULL
70	DROP TRIGGER dbo.trig_UPDATE_Stud
71GO
72CREATE TRIGGER trig_UPDATE_Stud
73	ON dbo.Studs
74	AFTER UPDATE
75	AS
76	IF (UPDATE(Faculty))
77		BEGIN
78			IF EXISTS(SELECT 1 FROM inserted WHERE Faculty NOT IN (SELECT Faculty FROM DB1.dbo.Faculties))
79			BEGIN
80				THROW 51000, 'Cannot UPDATE Faculty in Studs. New Faculty does not exists in table Faculties', 1
81				ROLLBACK TRANSACTION
82			END
83		END
84GO
85
86
87--Тригер вставки студентов
88IF OBJECT_ID('dbo.trig_INSERT_Stud', 'TR') IS NOT NULL
89	DROP TRIGGER dbo.trig_INSERT_Stud
90GO
91CREATE TRIGGER dbo.trig_INSERT_Stud
92	ON dbo.Studs
93	AFTER INSERT
94	AS
95	IF (UPDATE(Faculty))
96		BEGIN
97			IF EXISTS(SELECT 1 FROM inserted WHERE Faculty NOT IN (SELECT Faculty FROM DB1.dbo.Faculties))
98			BEGIN
99				THROW 51000, 'Cannot INSERT Faculty in Studs. New Faculty does not exists in table Faculties', 1
100				ROLLBACK TRANSACTION
101			END
102		END
103GO
104use DB1
105
106
107--Тесты
108GO
109INSERT INTO dbo.Faculties(Faculty,Dean)
110VALUES ('ICS-9', 'Ivanov'),
111('IBM-4', 'Petrov'),
112('RK-3', 'Vlasov')
113GO
114Use DB2
115GO
116INSERT INTO dbo.Studs(ID,name,Surname,Faculty)
117VALUES(1,'Ivan','Filonenko','ICS-9')
118GO
119INSERT INTO dbo.Studs(ID,name,Surname,Faculty)
120VALUES(2,'Oleg','Shatnuk','IBM-4')
121INSERT INTO dbo.Studs(ID,name,Surname,Faculty)
122VALUES(3,'Vasya','Rofionoff','RK-5')
123GO
124SELECT * FROM dbo.Studs
125GO
126UPDATE dbo.Studs
127SET Surname = 'Kek'
128WHERE Faculty = 'ICS-9'
129GO
130UPDATE dbo.Studs
131SET Faculty = 'ICS-9'
132WHERE Faculty = 'IBM-4'
133GO
134SELECT * FROM dbo.Studs
135GO
136use DB1
137GO
138SELECT * FROM dbo.Faculties
139GO
140UPDATE dbo.Faculties
141SET Faculty = 'RL-1'
142WHERE Faculty = 'ICS-9'
143GO
144UPDATE dbo.Faculties
145SET Faculty = 'RL-1'
146WHERE Faculty = 'IBM-4'
147GO
148SELECT * FROM dbo.Faculties
149GO
150DELETE FROM dbo.Faculties
151WHERE Faculty = 'ICS-9'
152GO
153DELETE FROM dbo.Faculties
154WHERE Dean = 'Petrov'
155GO
156SELECT * FROM dbo.Faculties
157GO
158
159
160--Представление Студенты и Факультеты
161USE DB2
162GO
163DROP VIEW dbo.StudFuc
164GO
165CREATE VIEW dbo.StudFuc
166AS
167SELECT studs.ID, studs.name, studs.Surname, fucs.Faculty, fucs.Dean
168FROM dbo.Studs studs
169RIGHT JOIN DB1.dbo.Faculties fucs
170ON studs.Faculty = fucs.Faculty
171GO
172SELECT * FROM dbo.StudFuc
173GO
174
175
176--Тригер вставки в представление
177IF OBJECT_ID('trig_INSERT_StudFuc', 'TR') IS NOT NULL
178	DROP TRIGGER trig_INSERT_StudFuc
179GO
180CREATE TRIGGER trig_INSERT_StudFuc
181	ON dbo.StudFuc
182	INSTEAD OF INSERT
183	AS 
184	BEGIN
185		SET NOCOUNT ON
186		INSERT INTO DB1.dbo.Faculties(
187			Faculty,
188			Dean
189		)
190		SELECT 
191			i.Faculty,
192			i.Dean
193		FROM inserted i
194		WHERE i.Faculty not IN (SELECT Faculty FROM DB1.dbo.Faculties)
195
196		INSERT INTO dbo.Studs(
197			ID,
198			name,
199			Surname,
200			Faculty
201		)
202		SELECT i.ID,
203			i.name,
204			i.Surname,
205			i.Faculty
206		FROM inserted i
207	END
208
209
210
211--Тригер удаления из представления
212GO
213IF OBJECT_ID('trig_DELETE_StudFuc', 'TR') IS NOT NULL
214	DROP TRIGGER trig_DELETE_StudFuc
215GO
216CREATE TRIGGER trig_DELETE_StudFuc
217	ON dbo.StudFuc
218	INSTEAD OF DELETE
219	AS 
220	BEGIN
221		DELETE FROM dbo.Studs WHERE ID in (SELECT ID FROM DELETED)
222	END
223
224GO
225
226
227--Тригер обновления представления
228IF OBJECT_ID('trig_UPDATE_StudFuc', 'TR') IS NOT NULL
229	DROP TRIGGER trig_UPDATE_StudFuc
230GO
231CREATE TRIGGER trig_UPDATE_StudFuc
232	ON dbo.StudFuc
233	INSTEAD OF UPDATE
234	AS 
235	BEGIN
236		DELETE FROM dbo.Studs WHERE ID in (SELECT ID FROM DELETED)
237		INSERT INTO dbo.Studs(ID, name, Surname, Faculty) SELECT ID, name, Surname, Faculty FROM inserted
238	END
239
240GO
241
242
243--Тесты
244INSERT INTO dbo.StudFuc(ID, name,Surname, Faculty,Dean)
245VALUES(3,'Alex','Gromkov','ICS-7','Durov')
246GO
247SELECT * FROM dbo.StudFuc
248GO
249DELETE FROM dbo.StudFuc
250WHERE name = 'Oleg'
251GO
252SELECT * FROM dbo.StudFuc
253GO
254UPDATE dbo.StudFuc
255SET Surname = 'Filonenko'
256WHERE ID = 1
257GO
258SELECT * FROM dbo.StudFuc
259GO
260INSERT INTO dbo.StudFuc(ID,name,Surname,Faculty,Dean)
261VALUES(4,'Artem', 'Egorichev', 'ICS-7', 'Durov');
262GO
263SELECT * FROM dbo.StudFuc
264GO
265UPDATE dbo.StudFuc
266SET Faculty = 'IBM-2'
267WHERE ID =1 
268GO
269SELECT * FROM dbo.StudFuc