БД 9 лаба

  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
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\lab9.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
USE lab9;
GO
CREATE TABLE country (
countryid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
namec nvarchar(30));
GO
CREATE TABLE ruler (
rulerid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
name nvarchar(30),
ruler_country int FOREIGN KEY REFERENCES country(countryid)
ON DELETE CASCADE
ON UPDATE CASCADE
);
GO
DECLARE @id varchar(30);
INSERT country (namec) VALUES ('Rome');
SET @id = SCOPE_IDENTITY();
INSERT ruler (ruler_country, name) VALUES (@id, 'Caesar');
INSERT ruler (ruler_country, name) VALUES (@id, 'Octavian');
INSERT country (namec) VALUES ('Greece');
SET @id = SCOPE_IDENTITY();
INSERT ruler (ruler_country, name) VALUES (@id, '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 (namec) VALUES ('China');
GO
CREATE TRIGGER update_country ON country AFTER UPDATE
AS PRINT 'update';
SELECT * FROM country;
GO
UPDATE country SET namec = 'India' WHERE namec = 'China';
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(namec) SELECT DISTINCT namec FROM inserted WHERE namec NOT IN (SELECT namec FROM country);
INSERT ruler(ruler_country, name) SELECT DISTINCT a.countryid, b.name FROM dbo.country a INNER JOIN inserted b ON a.namec = b.namec WHERE b.name NOT IN (SELECT name FROM ruler);
END;
GO
INSERT C_lView(namec, name) VALUES ('Egypt', 'Ramses'), ('Egypt', 'Ramses II');
INSERT C_lView(namec, name) VALUES ('Egypt', 'Ramses II');
INSERT C_lView(namec, 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 namec = '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
BEGIN
CREATE TABLE #table1 (id1 int IDENTITY PRIMARY KEY, new_name nvarchar(30));
CREATE TABLE #table2 (id2 int IDENTITY PRIMARY KEY, old_name nvarchar(30));
INSERT #table1 (new_name) SELECT name FROM inserted;
INSERT #table2 (old_name) SELECT name FROM deleted;
INSERT country(namec) SELECT DISTINCT namec FROM inserted WHERE namec NOT IN (SELECT namec FROM country);
UPDATE ruler SET ruler_country = countryid FROM country a INNER JOIN inserted b ON a.namec =b.namec WHERE ruler.name = b.name;
UPDATE ruler SET name = new_name FROM #table1 INNER JOIN #table2 ON #table1.id1 = #table2.id2 WHERE ruler.name = #table2.old_name;
DELETE FROM country WHERE countryid NOT IN (SELECT ruler_country FROM ruler);
DROP TABLE #table1;
DROP TABLE #table2;
END;
GO
INSERT C_lView(namec, name) VALUES ('Greece', 'Caligula');
SELECT * FROM country;
GO
SELECT * FROM ruler;
GO
SELECT * FROM C_lView;
GO
UPDATE C_lView SET namec = 'Rome II' WHERE namec = 'Rome';
UPDATE C_lView SET name = 'Caesar II' WHERE name = 'Santa';
UPDATE C_lView SET namec = 'Honduras' WHERE name like 'Ca%'
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