jQAtJL3b

1-- step 1
2
3USE LAB13_1;
4GO
5
6if OBJECT_ID(N'SCHOOL',N'U') is NOT NULL
7	DROP TABLE SCHOOL;
8go
9
10CREATE TABLE SCHOOL (
11	number_school int PRIMARY KEY NOT NULL,
12	address_sc char(50) NULL,
13	number_phone char(11) NULL);
14go
15
16USE LAB13_2
17GO
18
19if OBJECT_ID(N'SCHOOL',N'U') is NOT NULL
20	DROP TABLE SCHOOL;
21go
22
23CREATE TABLE SCHOOL (
24	number_school int PRIMARY KEY,
25	director char(50) NOT NULL);
26go
27
28if OBJECT_ID(N'VIEW_LAB14',N'V') is NOT NULL
29	DROP VIEW VIEW_LAB14;
30go
31
32CREATE VIEW VIEW_LAB14 AS
33	SELECT A.number_school, A.address_sc, A.number_phone, B.director
34	FROM LAB13_1.dbo.SCHOOL as A,
35		LAB13_2.dbo.SCHOOL as B
36	WHERE A.number_school = B.number_school
37go
38
39-- ТРИГЕР НА ВСТАВКУ
40
41IF OBJECT_ID(N'INSERT_SCHOOL') IS NOT NULL
42	DROP TRIGGER INSERT_SCHOOL
43GO
44
45CREATE TRIGGER INSERT_SCHOOL
46	ON VIEW_LAB14
47	INSTEAD OF INSERT AS
48	BEGIN
49		IF NOT EXISTS(SELECT A.number_school, A.address_sc, A.number_phone, B.director
50			FROM LAB13_1.dbo.SCHOOL AS A, LAB13_2.dbo.SCHOOL AS B, inserted AS I
51			WHERE A.number_school = I.number_school OR
52				A.address_sc = I.address_sc OR
53				A.number_phone = I.number_phone OR
54				B.director = I.director)
55			BEGIN
56				INSERT INTO LAB13_1.dbo.SCHOOL SELECT number_school, address_sc, number_phone FROM inserted
57				INSERT INTO LAB13_2.dbo.SCHOOL SELECT number_school, director FROM inserted
58			END
59		ELSE 
60			RAISERROR('Such values already exist in the database', 13, 3)
61	END
62GO
63
64INSERT INTO VIEW_LAB14(number_school, address_sc, number_phone, director)
65VALUES (1, '484 Drainer Avenue, Youngstown, Florida', 9035553213, 'David Copeland'),
66	   (42, '4538 Tail Ends Road, Waukesha, Wisconsin', 9203429491, 'Katherine J Murphy'),
67	   (101, '4439 Washington Street, Corpus Christi, Texas', 9235453900, 'Robert D. Banks'),
68	   (269, '2180 Dancing Dove Lane, New York, Alaska' , 96839538495, 'Stacey J. Martinez')
69go
70
71-- ТРИГЕР НА ИЗМЕНЕНИЕ
72
73IF OBJECT_ID(N'UPDATE_SCHOOL') IS NOT NULL
74	DROP TRIGGER UPDATE_SCHOOL
75GO
76
77CREATE TRIGGER UPDATE_SCHOOL
78	ON VIEW_LAB14
79	INSTEAD OF UPDATE AS
80	BEGIN
81		IF UPDATE(number_school) OR UPDATE(address_sc) OR UPDATE(number_phone)
82			RAISERROR('You can not change school number, phone number or(and) its address', 13, 3)
83		ELSE
84		BEGIN
85			IF NOT EXISTS(SELECT B.director
86			FROM LAB13_2.dbo.SCHOOL AS B, inserted AS I
87			WHERE B.director = I.director)
88			BEGIN
89				UPDATE LAB13_2.dbo.SCHOOL SET director = inserted.director  FROM inserted WHERE inserted.number_school = LAB13_2.dbo.SCHOOL.number_school
90			END
91			ELSE
92				RAISERROR('This director already exists in this database', 13, 3)
93		END
94	END
95GO
96
97UPDATE VIEW_LAB14
98SET director = 'Alex' WHERE number_school = 42
99GO
100
101-- ТРИГЕР НА УДАЛЕНИЕ 
102
103IF OBJECT_ID(N'DELETE_SCHOOL') IS NOT NULL
104	DROP TRIGGER DELETE_SCHOOL
105GO
106
107CREATE TRIGGER DELETE_SCHOOL
108	ON VIEW_LAB14
109	INSTEAD OF DELETE AS
110	BEGIN
111		DELETE FROM LAB13_1.dbo.SCHOOL WHERE number_school IN (SELECT number_school FROM deleted)
112		DELETE FROM LAB13_2.dbo.SCHOOL WHERE number_school IN (SELECT number_school FROM deleted)
113	END
114GO
115
116DELETE VIEW_LAB14
117WHERE number_school > 200
118GO 
119SELECT * FROM VIEW_LAB14
120GO
121
122SELECT * FROM LAB13_1.dbo.SCHOOL
123GO
124
125SELECT * FROM LAB13_2.dbo.SCHOOL
126GO