Guest User

lab15

a guest
Dec 14th, 2019
90
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. use master;
  2. go
  3. if DB_ID (N'lab15_1') is not null
  4. drop database lab15_1;
  5. go
  6. create database lab15_1
  7. on (
  8. NAME = lab151dat,
  9. FILENAME = 'C:\DB\lab15\lab151dat.mdf',
  10. SIZE = 10,
  11. MAXSIZE = 25,
  12. FILEGROWTH = 5
  13. )
  14. log on (
  15. NAME = lab151log,
  16. FILENAME = 'C:\DB\lab15\lab151log.ldf',
  17. SIZE = 5,
  18. MAXSIZE = 20,
  19. FILEGROWTH = 5
  20. );
  21. go
  22.  
  23. use master;
  24. go
  25. if DB_ID (N'lab15_2') is not null
  26. drop database lab15_2;
  27. go
  28. create database lab15_2
  29. on (
  30. NAME = lab152dat,
  31. FILENAME = 'C:\DB\lab15\lab152dat.mdf',
  32. SIZE = 10,
  33. MAXSIZE = 25,
  34. FILEGROWTH = 5
  35. )
  36. log on (
  37. NAME = lab152log,
  38. FILENAME = 'C:\DB\lab15\lab152log.ldf',
  39. SIZE = 5,
  40. MAXSIZE = 20,
  41. FILEGROWTH = 5
  42. );
  43. go
  44.  
  45. use lab15_1;
  46. go
  47.  
  48. if OBJECT_ID(N'Squad',N'U') is NOT NULL
  49. DROP TABLE Squad;
  50. go
  51.  
  52. CREATE TABLE Squad (
  53. Squad_Id int PRIMARY KEY NOT NULL,
  54. Surname char(30) NOT NULL,
  55. Name char(30) NOT NULL,
  56. Number int NOT NULL,
  57. Clubs_Id int NOT NULL
  58. );
  59. go
  60.  
  61. use lab15_2;
  62. go
  63.  
  64. if OBJECT_ID(N'Clubs',N'U') is NOT NULL
  65. DROP TABLE Clubs;
  66. go
  67.  
  68. CREATE TABLE Clubs(
  69. Clubs_Id int PRIMARY KEY NOT NULL,
  70. Title nchar(30) NOT NULL,
  71. Year_of_foundation numeric(4) NOT NULL
  72. );
  73. go
  74.  
  75. if OBJECT_ID(N'SquadClubsView',N'V') is NOT NULL
  76. DROP VIEW SquadClubsView;
  77. go
  78.  
  79. CREATE VIEW SquadClubsView AS
  80. SELECT A.Squad_Id as Squad_Id, A.Surname as Surname, A.Name as Name, A.number as Number, B.*
  81. FROM lab15_1.dbo.Squad A, lab15_2.dbo.Clubs B
  82. WHERE A.Clubs_Id = B.Clubs_Id
  83. go
  84.  
  85. IF OBJECT_ID(N'InsertClubs',N'TR') IS NOT NULL
  86. DROP TRIGGER InsertClubs
  87. go
  88.  
  89. CREATE TRIGGER InsertClubs
  90. ON Clubs
  91. INSTEAD OF INSERT
  92. AS
  93. BEGIN
  94. IF EXISTS(SELECT A.Clubs_Id FROM lab15_2.dbo.Clubs AS A,
  95. inserted as I
  96. WHERE A.Clubs_Id = I.Clubs_Id)
  97. BEGIN
  98. EXEC sp_addmessage 50002, 15,N'ID занят!',@lang='us_english',@replace='REPLACE';
  99. RAISERROR(50002,15,-1)
  100. END
  101. ELSE
  102. INSERT INTO lab15_2.dbo.Clubs(Clubs_Id, Title, Year_of_foundation)
  103. SELECT Clubs_Id, Title, Year_of_foundation FROM inserted
  104. IF (SELECT COUNT(*) FROM inserted) > 1
  105. PRINT 'Добавлены новые клубы'
  106. ELSE
  107. PRINT 'Добавлен новый клуб'
  108. END
  109. go
  110.  
  111.  
  112. IF OBJECT_ID(N'DeleteClubs',N'TR') IS NOT NULL
  113. DROP TRIGGER DeleteClubs
  114. go
  115.  
  116. CREATE TRIGGER DeleteClubs
  117. ON Clubs
  118. INSTEAD OF DELETE
  119. AS
  120. BEGIN
  121. DELETE B FROM lab15_1.dbo.Squad AS B INNER JOIN deleted AS d ON B.Clubs_Id = d.Clubs_Id
  122. DELETE A FROM lab15_2.dbo.Clubs AS A INNER JOIN deleted AS d ON A.Clubs_Id = d.Clubs_Id
  123. END
  124. go
  125.  
  126. IF OBJECT_ID(N'UpdateClubs',N'TR') IS NOT NULL
  127. DROP TRIGGER UpdateClubs
  128. go
  129.  
  130. CREATE TRIGGER UpdateClubs
  131. ON Clubs
  132. AFTER UPDATE
  133. AS
  134. BEGIN
  135. BEGIN;
  136. DECLARE @temp_table TABLE(
  137. Clubs_Id int PRIMARY KEY,
  138. add_Title char(30), add_Year_of_foundation numeric(4),
  139. delete_Title char(30), delete_Year_of_foundation numeric(4)
  140. );
  141.  
  142. INSERT INTO @temp_table(Clubs_Id, add_Title, add_Year_of_foundation, delete_Title, delete_Year_of_foundation)
  143. SELECT A.Clubs_Id, A.Title, A.Year_of_foundation,
  144. B.Title, B.Year_of_foundation
  145. FROM inserted A
  146. INNER JOIN deleted B ON A.Clubs_Id = B.Clubs_Id
  147. END;
  148. END
  149. go
  150.  
  151. use lab15_1;
  152. go
  153.  
  154. IF OBJECT_ID(N'InsertSquad',N'TR') IS NOT NULL
  155. DROP TRIGGER InsertSquad
  156. go
  157.  
  158. CREATE TRIGGER InsertSquad
  159. ON Squad
  160. INSTEAD OF INSERT
  161. AS
  162. BEGIN
  163. IF EXISTS (SELECT B.Squad_Id FROM lab15_1.dbo.Squad AS B,inserted AS I
  164. WHERE B.Squad_Id = I.Squad_Id)
  165. BEGIN
  166. EXEC sp_addmessage 50002, 15,N'ID занят! Попробуйте другой',@lang='us_english',@replace='REPLACE';
  167. RAISERROR(50002,15,-1)
  168. END
  169. ELSE
  170. INSERT INTO Squad(Squad_Id,Surname,Name, Number,Clubs_Id)
  171. SELECT Squad_Id, Surname, Name, Number, Clubs_Id FROM inserted
  172. END
  173. go
  174.  
  175. IF OBJECT_ID(N'DeleteSquad',N'TR') IS NOT NULL
  176. DROP TRIGGER DeleteSquad
  177. go
  178.  
  179. CREATE TRIGGER DeleteSquad
  180. ON Squad
  181. INSTEAD OF DELETE
  182. AS
  183. BEGIN
  184. DELETE B FROM lab15_1.dbo.Squad AS B INNER JOIN deleted AS d ON b.Squad_Id = d.Squad_Id
  185. END
  186. go
  187.  
  188.  
  189. IF OBJECT_ID(N'UpdateSquad',N'TR') IS NOT NULL
  190. DROP TRIGGER UpdateSquad
  191. go
  192.  
  193. CREATE TRIGGER UpdateSquad
  194. ON Squad
  195. AFTER UPDATE
  196. AS
  197. BEGIN
  198. DECLARE @temp_table TABLE(
  199. Clubs_Id int PRIMARY KEY,
  200. add_Number int,
  201. delete_Number int
  202. );
  203. INSERT INTO @temp_table(Clubs_Id, add_Number, delete_Number)
  204. SELECT A.Clubs_Id, A.Number, A.Number
  205. FROM inserted A
  206. INNER JOIN deleted B ON A.Clubs_Id = B.Clubs_Id
  207. END
  208. go
  209.  
  210. INSERT INTO lab15_2.dbo.Clubs(Clubs_Id, Title, Year_of_foundation)
  211. VALUES (1, N'1 Команда', 1337),
  212. (2, N'2 Команда', 1597),
  213. (3, N'3 Команда', 1889),
  214. (4, N'4 Команда', 1943)
  215. go
  216.  
  217. UPDATE lab15_2.dbo.Clubs SET Year_of_foundation = 9999 WHERE Clubs_Id = 2
  218. go
  219.  
  220. INSERT INTO lab15_1.dbo.Squad(Squad_Id,Surname,Name,Number,Clubs_Id)
  221. VALUES
  222. (1,N'Аррисабалага',N'Кепа',23,1),
  223. (2,N'Алонсо',N'Маркос',32,2),
  224. (3,N'Рюдигер',N'Антонио',5,3),
  225. (4,N'Томори',N'Фикайо',6,4)
  226. go
  227.  
  228. UPDATE lab15_1.dbo.Squad SET Number = 9999 WHERE Squad_Id = 1
  229. go
  230.  
  231. SELECT * FROM lab15_2.dbo.Clubs;
  232. SELECT * FROM lab15_1.dbo.Squad;
RAW Paste Data