· 6 years ago · Dec 10, 2019, 08:30 PM
1use master;
2go
3if DB_ID (N'lab14_1') is not null
4drop database lab14_1;
5go
6create database lab14_1
7on (
8NAME = lab141dat,
9FILENAME = 'C:\DB\lab14\lab141dat.mdf',
10SIZE = 10,
11MAXSIZE = 25,
12FILEGROWTH = 5
13)
14log on (
15NAME = lab141log,
16FILENAME = 'C:\DB\lab14\lab141log.ldf',
17SIZE = 5,
18MAXSIZE = 20,
19FILEGROWTH = 5
20);
21go
22
23use master;
24go
25if DB_ID (N'lab14_2') is not null
26drop database lab14_2;
27go
28create database lab14_2
29on (
30NAME = lab142dat,
31FILENAME = 'C:\DB\lab14\lab142dat.mdf',
32SIZE = 10,
33MAXSIZE = 25,
34FILEGROWTH = 5
35)
36log on (
37NAME = lab142log,
38FILENAME = 'C:\DB\lab14\lab142log.ldf',
39SIZE = 5,
40MAXSIZE = 20,
41FILEGROWTH = 5
42);
43go
44
45--================================================================--
46use lab14_1;
47go
48
49if OBJECT_ID(N'Books',N'U') is NOT NULL
50 DROP TABLE Books;
51go
52
53CREATE TABLE Books(
54 Books_Id int NOT NULL PRIMARY KEY,
55 Title char(30) NOT NULL,
56 Author char(30) NOT NULL
57)
58go
59
60use lab14_2;
61go
62
63if OBJECT_ID(N'Books',N'U') is NOT NULL
64 DROP TABLE Books;
65go
66
67CREATE TABLE Books(
68 Books_Id int NOT NULL PRIMARY KEY,
69 Year numeric(4) NOT NULL,
70 Cost money NOT NULL
71)
72go
73
74
75if OBJECT_ID(N'BooksView',N'V') is NOT NULL
76 DROP VIEW BooksView;
77go
78
79CREATE VIEW BooksView AS
80 SELECT A.Books_Id, A.Title, A.Author, B.Year, B.Cost
81 FROM lab14_1.dbo.Books A, lab14_2.dbo.Books B
82 WHERE A.Books_Id = B.Books_Id
83go
84
85IF OBJECT_ID(N'InsertBooksView',N'TR') IS NOT NULL
86 DROP TRIGGER InsertBooksView
87go
88
89CREATE TRIGGER InsertBooksView
90 ON BooksView
91 INSTEAD OF INSERT
92 AS
93 BEGIN
94 /*IF EXISTS (SELECT A.Books_Id
95 FROM lab14_1.dbo.Books AS A,
96 lab14_2.dbo.Books AS B,
97 inserted as ins
98 WHERE A.Title = ins.Title AND A.Author = ins.Author AND B.Year = ins.Year)
99 BEGIN
100 EXEC sp_addmessage 50003, 15,N'Добавление дубликата книги!',@lang='us_english',@replace='REPLACE';
101 RAISERROR(50003,15,-1)
102 END
103 ELSE*/
104
105 IF EXISTS (SELECT A.Books_Id
106 FROM lab14_1.dbo.Books AS A,
107 inserted AS ins
108 WHERE A.Books_Id = ins.Books_Id)
109 BEGIN
110 EXEC sp_addmessage 50004, 15,N'ID занят',@lang='us_english',@replace='REPLACE';
111 RAISERROR(50004,15,-1)
112 END
113 ELSE
114 BEGIN
115 INSERT INTO lab14_1.dbo.Books(Books_Id, Title, Author)
116 SELECT Books_Id, Title, Author FROM inserted
117
118 INSERT INTO lab14_2.dbo.Books(Books_Id, Year, Cost)
119 SELECT Books_Id, Year, Cost FROM inserted
120 END
121 END
122go
123
124INSERT INTO BooksView(Books_Id, Title, Author, Year, Cost)
125VALUES (1, N'Первая', N'Автор 1',1465, 500),
126 (2, N'Вторая', N'Автор 2',1445, 700),
127 (3, N'Третья', N'Автор 3',1897, 1500),
128 (4, N'Четвертая', N'Автор 4',1555, 350),
129 (5, N'Пятая', N'Автор 5',1224, 1337)
130go
131
132SELECT * FROM BooksView
133go
134--=======================================================================-
135
136IF OBJECT_ID(N'UpdateBooksView',N'TR') IS NOT NULL
137 DROP TRIGGER UpdateBooksView
138go
139
140CREATE TRIGGER UpdateBooksView
141 ON BooksView
142 INSTEAD OF UPDATE
143AS
144 BEGIN
145 IF UPDATE(Books_Id)
146 BEGIN
147 EXEC sp_addmessage 50001, 15,N'Запрещено изменение ID фильма',@lang='us_english',@replace='REPLACE';
148 RAISERROR(50001,15,-1)
149 END
150
151 DECLARE @temp_table TABLE(
152 Books_Id int,
153 add_cost money,
154 delete_cost money
155 );
156 INSERT INTO @temp_table(Books_Id, add_cost, delete_cost)
157 SELECT A.Books_Id, A.Cost, B.Cost
158 FROM inserted A
159 INNER JOIN deleted B ON A.Books_Id = B.Books_id
160
161 UPDATE lab14_2.dbo.Books SET Cost = inserted.Cost FROM inserted WHERE inserted.Books_Id = lab14_2.dbo.Books.Books_Id
162 END
163go
164
165UPDATE BooksView SET Cost = 12341234 WHERE Year > 1450
166SELECT * FROM BooksView
167go
168
169IF OBJECT_ID(N'DeleteBooksView',N'TR') IS NOT NULL
170 DROP TRIGGER DeleteBooksView
171go
172
173CREATE TRIGGER DeleteBooksView
174 ON BooksView
175 INSTEAD OF DELETE
176AS
177 BEGIN
178 DELETE C FROM lab14_1.dbo.Books AS C INNER JOIN deleted AS D ON C.Books_Id = D.Books_Id
179 DELETE C FROM lab14_2.dbo.Books AS C INNER JOIN deleted AS D ON C.Books_Id = D.Books_Id
180 END
181go
182
183DELETE FROM BooksView WHERE Author = 'Автор 2'
184SELECT * FROM BooksView
185go
186
187SELECT * FROM lab14_1.dbo.Books
188go
189
190SELECT * FROM lab14_2.dbo.Books
191go