· 6 years ago · Dec 14, 2019, 10:30 AM
1USE master
2GO
3
4IF DB_ID (N'lab15_1') IS NOT NULL
5DROP DATABASE lab15_1;
6GO
7IF DB_ID (N'lab15_2') IS NOT NULL
8DROP DATABASE lab15_2;
9GO
10
11CREATE DATABASE lab15_1
12 ON PRIMARY(
13 NAME = lab15_1_dat,
14 FILENAME = 'S:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\lab\lab15_1_dat.mdf',
15 SIZE = 10MB,
16 MAXSIZE = 100MB,
17 FILEGROWTH = 10MB )
18 LOG ON (
19 NAME = lab15_1_log,
20 FILENAME = 'S:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\lab\lab15_1_log.ldf',
21 SIZE = 5MB,
22 MAXSIZE = 25MB,
23 FILEGROWTH = 5MB );
24GO
25
26CREATE DATABASE lab15_2
27 ON PRIMARY(
28 NAME = lab15_2_dat,
29 FILENAME = 'S:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\lab\lab15_2_dat.mdf',
30 SIZE = 10MB,
31 MAXSIZE = 100MB,
32 FILEGROWTH = 10MB )
33 LOG ON (
34 NAME = lab15_2_log,
35 FILENAME = 'S:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\lab\lab15_2_log.ldf',
36 SIZE = 5MB,
37 MAXSIZE = 25MB,
38 FILEGROWTH = 5MB );
39GO
40
41USE lab15_1;
42GO
43
44IF OBJECT_ID(N'kind_detail') IS NOT NULL
45 DROP TABLE kind_detail;
46GO
47CREATE TABLE kind_detail (
48 id int NOT NULL,
49 title nvarchar(255) NOT NULL CONSTRAINT UQ_title_detail UNIQUE,
50 CONSTRAINT PK_id_kind_detail PRIMARY KEY (id));
51GO
52
53CREATE TRIGGER upd_k_det ON kind_detail AFTER UPDATE AS
54BEGIN
55 IF (UPDATE(id))
56 BEGIN
57 RAISERROR(N'You can not update id', 18, 10);
58 ROLLBACK;
59 END
60END
61GO
62
63CREATE TRIGGER deld_k_det ON kind_detail AFTER DELETE AS
64BEGIN
65 DELETE lab15_2.dbo.kind_detail_rec WHERE (EXISTS(SELECT * FROM deleted
66 WHERE (deleted.id = lab15_2.dbo.kind_detail_rec.id_p OR deleted.id = lab15_2.dbo.kind_detail_rec.id_c)));
67END
68GO
69
70USE lab15_2;
71GO
72
73IF OBJECT_ID(N'kind_detail_rec') IS NOT NULL
74 DROP TABLE kind_detail_rec;
75GO
76CREATE TABLE kind_detail_rec (
77 id_p int NOT NULL,
78 id_c int NOT NULL,
79 [count] int NULL,
80 CONSTRAINT PK_id_kind_detail PRIMARY KEY (id_p, id_c));
81GO
82
83CREATE TRIGGER ins_k_det_rec ON kind_detail_rec AFTER INSERT AS
84BEGIN
85 IF ((SELECT COUNT(*) FROM inserted) <> (SELECT COUNT(*) FROM inserted AS i
86 WHERE (EXISTS (SELECT * FROM lab15_1.dbo.kind_detail AS k WHERE (i.id_c = k.id))))
87 OR (SELECT COUNT(*) FROM inserted) <> (SELECT COUNT(*) FROM inserted AS i
88 WHERE (EXISTS (SELECT * FROM lab15_1.dbo.kind_detail AS k WHERE (i.id_p = k.id)))))
89 BEGIN
90 RAISERROR(N'This id does not exist', 18, 10);
91 ROLLBACK;
92 END
93END
94GO
95
96CREATE TRIGGER upd_k_det_rec ON kind_detail_rec AFTER UPDATE AS
97BEGIN
98 IF ((SELECT COUNT(*) FROM inserted) <> (SELECT COUNT(*) FROM inserted AS i
99 WHERE (EXISTS (SELECT * FROM lab15_1.dbo.kind_detail AS k WHERE (i.id_c = k.id))))
100 OR (SELECT COUNT(*) FROM inserted) <> (SELECT COUNT(*) FROM inserted AS i
101 WHERE (EXISTS (SELECT * FROM lab15_1.dbo.kind_detail AS k WHERE (i.id_p = k.id)))))
102 BEGIN
103 RAISERROR(N'This id does not exist', 18, 10);
104 ROLLBACK;
105 END
106END
107GO
108
109INSERT lab15_1.dbo.kind_detail VALUES (1, 'q'), (2, 'w'), (3, 'e'), (4, 'r'), (5, 't'), (6, 'y');
110GO
111SELECT * FROM lab15_1.dbo.kind_detail;
112INSERT kind_detail_rec VALUES (1, 6, 2), (2, 1, 1), (4, 5, 6), (2, 5, 3), (4, 3, 1);
113--INSERT lab15_2.dbo.kind_detail_rec VALUES (1, 7, 2);
114SELECT * FROM kind_detail_rec;
115UPDATE kind_detail_rec SET id_c = 6 WHERE (id_c = 5);
116SELECT * FROM kind_detail_rec;
117DELETE lab15_1.dbo.kind_detail WHERE (id%2 = 1);
118SELECT * FROM lab15_1.dbo.kind_detail;
119SELECT * FROM kind_detail_rec;
120GO