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
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
USE master;
GO
IF db_id (N'Bank_lab9') IS NOT NULL
DROP DATABASE Bank_lab9;
go
CREATE DATABASE Bank_lab9
ON (NAME = Bank_dat_lab9, FILENAME = 'C:\data\Bankdatlab9.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = Bank_log_lab9, FILENAME = 'C:\data\Bankloglab9.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
USE Bank_lab9;
GO
CREATE TABLE Human (idofperson int IDENTITY(0, 1) PRIMARY KEY,
firstname char(50),
secondname char(50),
location char(150),
phone int);
GO
ALTER TABLE Human ADD age int CHECK (age > 0);
GO
ALTER TABLE Human ADD email char(25) DEFAULT ('н/д');
GO
ALTER TABLE Human ADD dateofbirth datetime DEFAULT (getdate());
GO
CREATE TABLE Employee (uniqid uniqueidentifier PRIMARY KEY ROWGUIDCOL DEFAULT (newid()),
post char(50),
passnumber int,
experience int);
GO
CREATE TABLE Client (clientid int PRIMARY KEY,
accountnumber int);
GO
CREATE SEQUENCE ForClient START WITH 0 INCREMENT BY 1;
GO
INSERT Client (clientid, accountnumber)
VALUES (NEXT VALUE FOR ForClient, 1);
INSERT Client (clientid, accountnumber)
VALUES (NEXT VALUE FOR ForClient, 2);
GO
/*
SELECT * FROM Client;
GO
*/
CREATE TABLE Operation (idoper int PRIMARY KEY,
dt datetime);
GO
CREATE TABLE Credit (ido int FOREIGN KEY REFERENCES Operation(idoper)
ON DELETE CASCADE
ON UPDATE CASCADE,
sumofmoney money,
procent decimal DEFAULT (15) NOT NULL);
GO
INSERT Operation (idoper, dt) VALUES (1, '20131201');
INSERT Operation (idoper, dt) VALUES (2, '20131222');
INSERT Operation (idoper, dt) VALUES (3, '20140111');
INSERT Operation (idoper, dt) VALUES (4, '20140112');
INSERT Credit(ido, sumofmoney, procent) VALUES (1, $25000, 5.0);
INSERT Credit(ido, sumofmoney, procent) VALUES (2, $1000000, 15.0);
INSERT Credit(ido, sumofmoney, procent) VALUES (3, $1000, 5.0);
GO
--Create view with schemabinding.
IF OBJECT_ID ('IndexView', 'view') IS NOT NULL
DROP VIEW IndexView;
GO
CREATE VIEW IndexView WITH SCHEMABINDING AS
SELECT a.idoper, a.dt, b.sumofmoney FROM
dbo.Operation a INNER JOIN dbo.Credit b ON a.idoper = b.ido;
GO
/*SELECT * FROM IndexView;
GO
*/
-- LAB 9
/*
Для одной из таблиц пункта 2 задания 7
создать триггеры на вставку, удаление и
добавление, при выполнении заданных
условий один из триггеров должен
инициировать возникновение ошибки
(RAISERROR / THROW).
*/
--1.1
IF OBJECT_ID ('insert_in_Operation','TR') IS NOT NULL
DROP TRIGGER insert_in_Operation;
GO
CREATE TRIGGER insert_in_Operation ON Operation FOR INSERT
AS PRINT N'New values have inserted in Operation.';
GO
--1.2
IF OBJECT_ID ('update_on_Operation','TR') IS NOT NULL
DROP TRIGGER update_on_Operation;
GO
CREATE TRIGGER update_on_Operation ON Operation AFTER UPDATE
AS SELECT * FROM Operation;
GO
--1.3
IF OBJECT_ID ('delete_in_Operation','TR') IS NOT NULL
DROP TRIGGER delete_in_Operation;
GO
CREATE TRIGGER delete_in_Operation ON Operation INSTEAD OF DELETE
AS THROW 51000, 'Cant delete.', 1;
GO
INSERT Operation (idoper, dt) VALUES (6, '20130405');
--UPDATE Operation SET idoper = 5 WHERE idoper = 6;
--DELETE FROM Operation WHERE dt = 5;
GO
DROP TRIGGER insert_in_Operation;
DROP TRIGGER update_on_Operation;
DROP TRIGGER delete_in_Operation;
GO
/*
Для представления пункта 2 задания 7
создать триггеры на вставку, удаление и
добавление, обеспечивающие возможность
выполнения операций с данными
непосредственно через представление.
*/
--2.1
IF OBJECT_ID ('insert_IndexView','TR') IS NOT NULL
DROP TRIGGER insert_IndexView;
GO
CREATE TRIGGER insert_IndexView ON IndexView INSTEAD OF INSERT
AS IF NOT EXISTS(SELECT * FROM Operation JOIN inserted ON Operation.idoper = inserted.idoper)
BEGIN
INSERT Operation(idoper, dt) SELECT idoper, dt FROM inserted;
INSERT Credit(ido, sumofmoney) SELECT idoper, sumofmoney FROM inserted;
END;
GO
--2.2
IF OBJECT_ID ('delete_IndexView','TR') IS NOT NULL
DROP TRIGGER delete_IndexView;
GO
CREATE TRIGGER delete_IndexView ON IndexView INSTEAD OF DELETE
AS DELETE FROM Operation WHERE idoper IN (SELECT idoper FROM deleted);
DELETE FROM Credit WHERE ido IN (SELECT idoper FROM deleted);
GO
--2.3
IF OBJECT_ID ('update_IndexView','TR') IS NOT NULL
DROP TRIGGER update_IndexView;
GO
CREATE TRIGGER update_IndexView ON IndexView INSTEAD OF UPDATE
AS
IF (UPDATE(idoper))
BEGIN
IF EXISTS (SELECT * FROM Operation JOIN inserted ON inserted.idoper = Operation.idoper)
BEGIN
RAISERROR('PK exists', 16, 1);
END
ELSE
BEGIN
CREATE TABLE curtab1 (id1 int IDENTITY PRIMARY KEY, idnew int);
CREATE TABLE curtab2 (id2 int IDENTITY PRIMARY KEY, idold int);
INSERT curtab1 (idnew) SELECT idoper FROM inserted;
INSERT curtab2 (idold) SELECT idoper FROM deleted;
UPDATE Operation SET idoper = idnew
FROM curtab1 INNER JOIN curtab2 ON curtab1.id1 = curtab2.id2
WHERE Operation.idoper = curtab2.idold;
UPDATE Credit SET ido = idnew
FROM curtab1 INNER JOIN curtab2 ON curtab1.id1 = curtab2.id2
WHERE Credit.ido = curtab2.idold;
DROP TABLE curtab1;
DROP TABLE curtab2;
END;
END;
GO
INSERT IndexView(idoper, dt, sumofmoney) VALUES (1, '20130908', $4000);
INSERT IndexView(idoper, dt, sumofmoney) VALUES (144, '20130908', $4000);
GO
SELECT * FROM Operation;
GO
SELECT * FROM Credit;
GO
DELETE FROM IndexView WHERE idoper = 144;
GO
SELECT * FROM Operation;
GO
SELECT * FROM Credit;
GO
UPDATE IndexView SET idoper = 100 WHERE idoper = 2;
GO
SELECT * FROM Operation;
GO
SELECT * FROM Credit;
GO
IF OBJECT_ID ('IndexView', 'view') IS NOT NULL
DROP VIEW IndexView;
GO
CREATE VIEW IndexView WITH SCHEMABINDING AS
SELECT a.idoper, a.dt, b.sumofmoney FROM
dbo.Operation a INNER JOIN dbo.Credit b ON a.idoper = b.ido;
GO
SELECT * FROM IndexView;
GO