БД 14 лаба

  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
USE MASTER;
GO
IF db_id (N'lab141') IS NOT NULL
DROP DATABASE lab141;
GO
CREATE DATABASE lab141
ON (NAME = lab141_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab141.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = lab141_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab141.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
IF db_id (N'lab142') IS NOT NULL
DROP DATABASE lab142;
GO
CREATE DATABASE lab142
ON (NAME = lab142_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab142.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = lab142_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab142.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
USE lab141;
GO
CREATE TABLE table1 (
ID int PRIMARY KEY,
ColumnA int );
GO
USE lab142;
GO
CREATE TABLE table1 (
ID int PRIMARY KEY,
ColumnB int);
GO
USE MASTER;
GO
CREATE VIEW view1
AS
SELECT a.ID, a.ColumnA, b.ColumnB FROM lab141.dbo.table1 a,
lab142.dbo.table1 b WHERE a.ID = b.ID;
GO
CREATE TRIGGER insert_view ON view1 INSTEAD OF INSERT
AS
INSERT lab141.dbo.table1 (ID, ColumnA) SELECT b.ID, b.ColumnA FROM inserted b;
INSERT lab142.dbo.table1 (ID, ColumnB) SELECT b.ID, b.ColumnB FROM inserted b;
GO
INSERT view1 (ID, ColumnA, ColumnB) VALUES (1, 1, 1), (2, 2, 1), (3, 3, 3), (4, 4, 4);
GO
CREATE TRIGGER delete_view ON view1 INSTEAD OF DELETE
AS
DELETE lab141.dbo.table1 WHERE ID IN (SELECT ID FROM deleted);
DELETE lab142.dbo.table1 WHERE ID IN (SELECT ID FROM deleted);
GO
DELETE view1 WHERE ID = 3;
GO
CREATE TRIGGER update_view ON view1 INSTEAD OF UPDATE
AS
BEGIN
CREATE TABLE #table1 (id1 int IDENTITY PRIMARY KEY, new_ID int, nA int, nB int);
CREATE TABLE #table2 (id2 int IDENTITY PRIMARY KEY, old_ID int, oA int, oB int);
INSERT #table1 (new_ID, nA, nB) SELECT * FROM inserted;
INSERT #table2 (old_ID, oA, oB) SELECT * FROM deleted;
UPDATE lab141.dbo.table1 SET lab141.dbo.table1.ID = #table1.new_ID, lab141.dbo.table1.ColumnA = #table1.nA
FROM #table1 INNER JOIN #table2 ON id1=id2 WHERE #table2.old_ID = lab141.dbo.table1.ID
UPDATE lab142.dbo.table1 SET lab142.dbo.table1.ID = #table1.new_ID, lab142.dbo.table1.ColumnB = #table1.nB
FROM #table1 INNER JOIN #table2 ON id1=id2 WHERE #table2.old_ID = lab142.dbo.table1.ID
END;
GO
UPDATE view1 SET ID = 5 WHERE ID = 1;
UPDATE view1 SET ColumnA = 6 WHERE ColumnA = 1;
UPDATE view1 SET ColumnB = 5 WHERE ColumnB = 1;
GO
SELECT * FROM view1;
UPDATE view1 SET ColumnB = 6 WHERE id = 5
SELECT * FROM view1;
SELECT * FROM view1;
GO
SELECT * FROM lab141.dbo.table1;
GO
SELECT * FROM lab142.dbo.table1;
GO
DROP VIEW view1;
GO
DROP TABLE lab141.dbo.table1;
GO
DROP TABLE lab142.dbo.table1;
GO