USE MASTER GO IF db_id lab9 IS NOT NULL DROP DATABASE lab9 go CREATE D

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
USE MASTER;
GO
IF db_id (N'lab9') IS NOT NULL
DROP DATABASE lab9;
go
CREATE DATABASE lab9
ON (NAME = lab9_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab9.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = lab9_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
USE lab9;
GO
CREATE TABLE country (
countryid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
namec char(30));
GO
CREATE TABLE ruler (
rulerid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
name char(30),
ruler_country int FOREIGN KEY REFERENCES country(countryid)
ON DELETE CASCADE
ON UPDATE CASCADE
);
GO
INSERT country (nameс) VALUES ('Rome');
INSERT country (nameс) VALUES ('Greece');
INSERT ruler (ruler_country, name) VALUES (1, 'Caesar');
INSERT ruler (ruler_country, name) VALUES (1, 'Octavian');
INSERT ruler (ruler_country, name) VALUES (2, 'Pericles');
GO
CREATE VIEW C_lView WITH SCHEMABINDING AS
SELECT a.namec, b.name FROM
dbo.country a INNER JOIN dbo.ruler b ON a.countryid = b.ruler_country
GO
-- вставка
CREATE TRIGGER insert_country ON country FOR INSERT
AS PRINT 'insert';
SELECT * FROM country;
GO
-- пример
/*INSERT country (countryid, nameс) VALUES (5, 'China');
GO*/
-- изменение
CREATE TRIGGER update_country ON country AFTER UPDATE
AS PRINT 'update';
SELECT * FROM country;
GO
-- пример
/*UPDATE country SET countryid = 10 WHERE countryid = 5;
GO*/
-- удаление
CREATE TRIGGER delete_country ON country INSTEAD OF DELETE
AS THROW 51000, 'Cant delete', 1;
GO
-- пример
/*DELETE FROM country WHERE countryid = 1;
GO*/
DROP TRIGGER insert_country;
DROP TRIGGER update_country;
DROP TRIGGER delete_country;
GO
-- вставка через представление
CREATE TRIGGER insert_C_lView ON C_lView INSTEAD OF INSERT
AS
BEGIN
INSERT country(nameс) SELECT nameс FROM inserted WHERE nameс NOT IN (SELECT nameс FROM country);
CREATE TABLE table1 (ins_namec char(30), ins_name char(30),);
INSERT table1 (ins_namec, ins_name) SELECT nameс, name FROM inserted;
INSERT ruler(ruler_country, name) SELECT DISTINCT a.countryid, b.ins_name FROM dbo.country a INNER JOIN dbo.table1 b ON a.nameс = b.ins_namec WHERE b.ins_name NOT IN (SELECT name FROM ruler);
DROP TABLE table1;
END;
GO
-- пример
INSERT C_lView(nameс, name) VALUES ('Egypt', 'Ramses');
INSERT C_lView(nameс, name) VALUES ('Egypt', 'Ramses II');
INSERT C_lView(nameс, name) VALUES ('Egypt', 'Ramses III'), ('Laplandia', 'Santa'), ('Egypt', 'Ramses V'), ('Egypt', 'Ramses V');
GO
SELECT * FROM country;
GO
SELECT * FROM ruler;
GO
SELECT * FROM C_lView;
GO
-- удаление через представление
CREATE TRIGGER delete_C_lView ON C_lView INSTEAD OF DELETE
AS
DELETE FROM ruler WHERE name IN (SELECT name FROM deleted);
DELETE FROM country WHERE countryid NOT IN (SELECT ruler_country FROM ruler);
GO
-- пример
DELETE FROM C_lView WHERE name = 'Caesar';
DELETE FROM C_lView WHERE nameс = 'Egypt';
GO
SELECT * FROM country;
GO
SELECT * FROM ruler;
GO
SELECT * FROM C_lView;
GO
-- изменение через представление
CREATE TRIGGER update_C_lView ON C_lView INSTEAD OF UPDATE
AS IF (UPDATE(nameс))
BEGIN
IF EXISTS (SELECT * FROM country JOIN inserted ON inserted.nameс = country.nameс)
BEGIN
THROW 51000, 'Cant update', 1;
END
ELSE
BEGIN
CREATE TABLE table1 (id1 int IDENTITY PRIMARY KEY, new_namec char(30));
CREATE TABLE table2 (id2 int IDENTITY PRIMARY KEY, old_namec char(30));
INSERT table1 (new_namec) SELECT nameс FROM inserted;
INSERT table2 (old_namec) SELECT nameс FROM deleted;
UPDATE country SET nameс = new_namec
FROM table1 INNER JOIN table2 ON table1.id1 = table2.id2
WHERE country.nameс = table2.old_namec;
DROP TABLE table1;
DROP TABLE table2;
END;
END;
IF (UPDATE(name))
BEGIN
IF EXISTS (SELECT * FROM ruler JOIN inserted ON inserted.name = ruler.name)
BEGIN
THROW 51000, 'Cant update', 1;
END
ELSE
BEGIN
CREATE TABLE table1 (id1 int IDENTITY PRIMARY KEY, new_name char(30));
CREATE TABLE table2 (id2 int IDENTITY PRIMARY KEY, old_name char(30));
INSERT table1 (new_name) SELECT name FROM inserted;
INSERT table2 (old_name) SELECT name FROM deleted;
UPDATE ruler SET name = new_name
FROM table1 INNER JOIN table2 ON table1.id1 = table2.id2
WHERE ruler.name = table2.old_name;
DROP TABLE table1;
DROP TABLE table2;
END;
END;
GO
-- пример
UPDATE C_lView SET nameс = 'Rome II' WHERE nameс = 'Rome';
UPDATE C_lView SET name = 'Caesar II' WHERE name = 'Santa';
GO
SELECT * FROM country;
GO
SELECT * FROM ruler;
GO
SELECT * FROM C_lView;
GO
DROP TRIGGER insert_C_lView;
DROP TRIGGER delete_C_lView;
DROP TRIGGER update_C_lView;
GO