1aWSYUVA

1USE master
2GO
3 
4IF DB_ID(N'lab15') IS NOT NULL
5DROP DATABASE lab15
6GO
7 
8CREATE DATABASE lab15
9GO
10 
11 
12IF DB_ID(N'lab_15') IS NOT NULL
13DROP DATABASE lab_15
14GO
15 
16CREATE DATABASE lab_15
17GO
18
19use lab15
20go
21
22IF OBJECT_ID(N'BookAuthors') IS NOT NULL
23DROP TABLE BookAuthors
24GO
25
26CREATE TABLE BookAuthors
27	(AuthorID INTEGER not null PRIMARY KEY,
28	FirstName varchar(30) not null,
29	LastName varchar(30) not null
30	);
31GO
32
33INSERT lab15.dbo.BookAuthors
34VALUES (1, 'Nikolay', 'Gogol'),
35	(2, 'Taras', 'Shevchenko')
36go
37
38select * from BookAuthors
39go
40
41CREATE TRIGGER trigger_update 
42ON lab15.dbo.BookAuthors 
43AFTER UPDATE AS
44BEGIN
45    IF (UPDATE(AuthorID))
46    BEGIN
47        RAISERROR(N'You can not update id', 16, 1);
48        ROLLBACK;
49    END
50END
51GO
52
53
54
55CREATE TRIGGER trigger_delete 
56ON lab15.dbo.BookAuthors 
57AFTER DELETE AS
58BEGIN
59   DELETE Lab_15.dbo.Book WHERE (EXISTS(SELECT * FROM deleted
60	WHERE (deleted.AuthorID = Lab_15.dbo.Book.AuthorID)))
61END
62GO
63
64
65use lab_15
66go
67
68IF OBJECT_ID(N'Book') IS NOT NULL
69DROP TABLE Book
70GO
71
72
73CREATE TABLE Book(
74	AuthorID INTEGER PRIMARY KEY,
75	[NameBook] varchar(100) not null
76	);
77GO
78
79INSERT Book
80VALUES (3, N'Мертвые души'),
81	(2, 'Кобзар')
82go
83
84select * from lab_15.dbo.Book
85go
86
87CREATE TRIGGER trigger_insert
88ON lab_15.dbo.Book
89AFTER INSERT AS
90BEGIN
91	IF((SELECT COUNT(*) FROM inserted) <> (SELECT COUNT(*) FROM inserted 
92		WHERE (EXISTS (SELECT * FROM lab15.dbo.BookAuthors AS l1 
93		WHERE (inserted.AuthorID = l1.AuthorID)))))
94	BEGIN
95		RAISERROR(N'You can`t INSERT', 16, 1);
96        ROLLBACK;
97	END
98END
99GO
100
101INSERT lab15.dbo.BookAuthors
102VALUES (3, 'Nikolay', 'Gogol')
103select * from lab15.dbo.BookAuthors
104select * from lab_15.dbo.Book
105go
106
107
108CREATE TRIGGER trigger_update_2
109ON lab_15.dbo.Book
110AFTER UPDATE AS
111BEGIN
112	IF((SELECT COUNT(*) FROM inserted) <> (SELECT COUNT(*) FROM inserted 
113		WHERE (EXISTS (SELECT * FROM lab15.dbo.BookAuthors AS l1 
114		WHERE (inserted.AuthorID = l1.AuthorID)))))
115	BEGIN
116		RAISERROR(N'Can`t find id', 16, 1);
117        ROLLBACK;
118	END
119END
120
121
122UPDATE lab_15.dbo.Book SET AuthorID = 4 WHERE (AuthorID = 3)
123select * from lab_15.dbo.Book
124go
125
126DELETE lab15.dbo.BookAuthors WHERE (AuthorID = 2)
127select * from lab15.dbo.BookAuthors
128select * from lab_15.dbo.Book
129go