SHARE
TWEET

lab15

a guest Dec 14th, 2019 81 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
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top