F8CJ1uXT

1use master;
2go
3CREATE DATABASE Lab15_1Database
4ON (NAME = "Lab15_1", FILENAME = "C:\Data\lab151.mdf", SIZE = 10, MAXSIZE = 125, FILEGROWTH = 5%)
5LOG ON (NAME = "Lab15_1_log",FILENAME = "C:\Data\lab151.ldf",SIZE = 5, MAXSIZE = 25, FILEGROWTH = 5);
6GO
7
8use master;
9go
10CREATE DATABASE Lab15_2Database
11ON (NAME = "Lab15_2", FILENAME = "C:\Data\lab152.mdf", SIZE = 10, MAXSIZE = 125, FILEGROWTH = 5%)
12LOG ON (NAME = "Lab15_2_log",FILENAME = "C:\Data\lab152.ldf",SIZE = 5, MAXSIZE = 25, FILEGROWTH = 5);
13GO
14
15USE Lab15_1Database;
16GO
17
18INSERT INTO Users VALUES ('sample2@sample2.ru', 'asdsasad13ds');
19
20select * from Users
21
22CREATE TABLE Users
23(
24	Id int NOT NULL PRIMARY KEY IDENTITY,
25	Email varchar(20) NOT NULL,
26	Pass varchar(20) NOT NULL,
27);
28
29GO
30
31USE Lab15_2Database;
32GO
33Insert into Comments values ('BAAD!', 2);
34
35select * from Comments
36
37
38CREATE TABLE Comments
39(
40	Id int NOT NULL PRIMARY KEY IDENTITY,
41	Content varchar(20) NOT NULL,
42	CreatorId int NOT NULL,
43);
44
45GO
46
47USE Lab15_1Database;
48GO
49
50CREATE TRIGGER deleteUsersTrigger
51    ON Lab15_1Database.dbo.Users
52    after DELETE
53    AS BEGIN
54        DELETE Lab15_2Database.dbo.Comments
55        WHERE CreatorId IN (SELECT Id FROM deleted)
56    END
57GO
58
59USE Lab15_2Database;
60GO
61ALTER TRIGGER insertCommentsTrigger
62    ON Lab15_2Database.dbo.Comments
63    instead OF INSERT
64    AS
65    BEGIN
66        IF (EXISTS (
67            SELECT CreatorId FROM inserted
68            EXCEPT
69            SELECT Id FROM  Lab15_1Database.dbo.Users
70        ))
71            THROW 123456, 'No foreign key', 1
72        ELSE BEGIN
73            INSERT INTO Comments SELECT Content, CreatorId FROM inserted
74        END
75 
76    END
77GO;
78
79insert into Comments values ('azaza', 5);
80
81ALTER TRIGGER updateCommentsTrigger
82    ON Lab15_2Database.dbo.Comments
83    instead OF UPDATE
84    AS
85    BEGIN
86        IF (EXISTS (
87            SELECT CreatorId FROM inserted
88            EXCEPT
89            SELECT Id FROM Lab15_1Database.dbo.Users
90        ))
91            THROW 123456, 'No foreign key', 1
92        ELSE BEGIN
93            UPDATE Lab15_2Database.dbo.Comments SET CreatorId = a.CreatorId FROM inserted AS a, deleted as b WHERE Lab15_2Database.dbo.Comments.Id = b.Id 
94            UPDATE Lab15_2Database.dbo.Comments SET Content = a.Content FROM inserted AS a, deleted as b WHERE Lab15_2Database.dbo.Comments.Id = b.Id 
95        END
96 
97    END
98GO;
99
100USE Lab15_1Database;
101GO
102select * from Users
103
104USE Lab15_2Database;
105GO
106select * from Comments
107
108UPDATE Comments set Content='AZAZ' WHERE Id=3;