· 6 years ago · Dec 08, 2019, 09:56 AM
1USE master
2GO
3
4IF DB_ID (N'lab14_1') IS NOT NULL
5DROP DATABASE lab14_1;
6GO
7IF DB_ID (N'lab14_2') IS NOT NULL
8DROP DATABASE lab14_2;
9GO
10
11CREATE DATABASE lab14_1
12 ON PRIMARY(
13 NAME = lab14_1_dat,
14 FILENAME = 'S:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\lab\lab14_1_dat.mdf',
15 SIZE = 10MB,
16 MAXSIZE = 100MB,
17 FILEGROWTH = 10MB )
18 LOG ON (
19 NAME = lab14_1_log,
20 FILENAME = 'S:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\lab\lab14_1_log.ldf',
21 SIZE = 5MB,
22 MAXSIZE = 25MB,
23 FILEGROWTH = 5MB );
24GO
25
26CREATE DATABASE lab14_2
27 ON PRIMARY(
28 NAME = lab14_2_dat,
29 FILENAME = 'S:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\lab\lab14_2_dat.mdf',
30 SIZE = 10MB,
31 MAXSIZE = 100MB,
32 FILEGROWTH = 10MB )
33 LOG ON (
34 NAME = lab14_2_log,
35 FILENAME = 'S:\Program Files\Microsoft SQL Server\MSSQL14.SQLEXPRESS\MSSQL\DATA\lab\lab14_2_log.ldf',
36 SIZE = 5MB,
37 MAXSIZE = 25MB,
38 FILEGROWTH = 5MB );
39GO
40
41USE lab14_1;
42GO
43
44IF OBJECT_ID(N'kind_detail_title') IS NOT NULL
45 DROP TABLE kind_detail_title;
46GO
47CREATE TABLE kind_detail_title (
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
53INSERT kind_detail_title VALUES (1, 'qwerty'), (2, 'asdfg'), (3, 'yuio');
54GO
55
56USE lab14_2;
57GO
58
59IF OBJECT_ID(N'kind_detail_specification') IS NOT NULL
60 DROP TABLE kind_detail_specification;
61GO
62CREATE TABLE kind_detail_specification (
63 id int NOT NULL,
64 specification nvarchar(255) NOT NULL,
65 CONSTRAINT PK_id_kind_detail PRIMARY KEY (id));
66GO
67
68INSERT kind_detail_specification VALUES (1, 'TY1234'), (2, 'TY2332');
69GO
70
71IF OBJECT_ID(N'kind_detail_all') IS NOT NULL
72 DROP VIEW kind_detail_all;
73GO
74CREATE VIEW kind_detail_all AS
75SELECT t.id AS id, t.title AS title, s.specification AS specification FROM lab14_2.dbo.kind_detail_specification AS s
76 RIGHT JOIN lab14_1.dbo.kind_detail_title AS t ON (t.id = s.id);
77GO
78SELECT * FROM kind_detail_all;
79GO
80
81CREATE TRIGGER ins_k_detail_all ON kind_detail_all INSTEAD OF INSERT AS
82BEGIN
83 IF (EXISTS (SELECT * FROM inserted AS i WHERE (EXISTS (SELECT *
84 FROM lab14_1.dbo.kind_detail_title AS k WHERE (k.id = i.id)))))
85 BEGIN
86 RAISERROR(N'Repeat id', 18, 10);
87 ROLLBACK;
88 END
89 INSERT lab14_1.dbo.kind_detail_title SELECT id, title FROM inserted;
90 INSERT lab14_2.dbo.kind_detail_specification SELECT id, specification
91 FROM inserted WHERE specification IS NOT NULL;
92END
93GO
94
95CREATE TRIGGER del_k_detail_all ON kind_detail_all INSTEAD OF DELETE AS
96BEGIN
97 DELETE lab14_1.dbo.kind_detail_title WHERE EXISTS(SELECT * FROM deleted
98 WHERE (lab14_1.dbo.kind_detail_title.id = deleted.id));
99 DELETE lab14_2.dbo.kind_detail_specification WHERE EXISTS(SELECT * FROM deleted
100 WHERE (lab14_2.dbo.kind_detail_specification.id = deleted.id));
101END
102GO
103
104CREATE TRIGGER upd_k_detail_all ON kind_detail_all INSTEAD OF UPDATE AS
105BEGIN
106 IF (UPDATE(id))
107 BEGIN
108 RAISERROR(N'You can not update id', 18, 10);
109 ROLLBACK;
110 END
111 IF (UPDATE(title))
112 BEGIN
113 UPDATE lab14_1.dbo.kind_detail_title SET title = (SELECT title FROM inserted
114 WHERE (inserted.id = lab14_1.dbo.kind_detail_title.id))
115 WHERE (EXISTS (SELECT * FROM inserted WHERE (lab14_1.dbo.kind_detail_title.id = inserted.id)));
116 END
117 IF (UPDATE(specification))
118 BEGIN
119 UPDATE lab14_2.dbo.kind_detail_specification SET specification = (SELECT specification FROM inserted
120 WHERE (inserted.id = lab14_2.dbo.kind_detail_specification.id))
121 WHERE (EXISTS (SELECT * FROM inserted
122 WHERE (lab14_2.dbo.kind_detail_specification.id = inserted.id AND specification IS NOT NULL)));
123 INSERT lab14_2.dbo.kind_detail_specification SELECT id, specification FROM inserted AS i
124 WHERE (EXISTS(SELECT * FROM deleted AS d WHERE (d.id = i.id AND
125 d.specification IS NULL AND i.specification IS NOT NULL)));
126 DELETE lab14_2.dbo.kind_detail_specification WHERE (EXISTS (SELECT * FROM inserted
127 WHERE (lab14_2.dbo.kind_detail_specification.id = inserted.id AND specification IS NULL)));
128 END
129END
130GO
131
132INSERT kind_detail_all VALUES (4, 'zxcv', NULL), (5, 'zxcgv', 'TY7253');
133SELECT * FROM kind_detail_all;
134GO
135UPDATE kind_detail_all SET title = 'kva-kva' WHERE (id%5 = 1);
136UPDATE kind_detail_all SET specification = 'kva-kva' WHERE (specification IS NULL);
137SELECT * FROM kind_detail_all;
138GO
139DELETE kind_detail_all WHERE (id%4 = 1);
140SELECT * FROM kind_detail_all;
141GO