БД 15 лаба

  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
USE MASTER;
GO
IF db_id (N'lab151') IS NOT NULL
DROP DATABASE lab151;
GO
CREATE DATABASE lab151
ON (NAME = lab151_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab151.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = lab151_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab151.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
IF db_id (N'lab152') IS NOT NULL
DROP DATABASE lab152;
GO
CREATE DATABASE lab152
ON (NAME = lab152_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab152.mdf',
SIZE = 10MB, MAXSIZE = 50MB, FILEGROWTH = 5MB)
LOG ON (NAME = lab152_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA\lab152.ldf',
SIZE = 5MB, MAXSIZE = 25MB, FILEGROWTH = 5MB);
GO
USE lab151;
GO
CREATE TABLE country (countryid int IDENTITY (1, 1) PRIMARY KEY NOT NULL,
namec char(30));
GO
CREATE TRIGGER deletecountry ON lab151.dbo.country AFTER DELETE
AS
DELETE lab152.dbo.ruler WHERE ruler_country IN (SELECT countryid FROM deleted);
GO
CREATE TRIGGER updatecountry ON lab151.dbo.country INSTEAD OF UPDATE
AS
RAISERROR ('prohibited', 16, 1);
GO
USE lab152;
GO
CREATE TABLE ruler (rulerid int IDENTITY (1, 1) PRIMARY KEY NOT NULL,
namer char(30),
ruler_country int);
GO
CREATE TRIGGER inserruler ON lab152.dbo.ruler INSTEAD OF INSERT
AS
BEGIN
IF EXISTS (SELECT ruler_country FROM inserted WHERE ruler_country NOT IN (SELECT lab151.dbo.country.countryid FROM lab151.dbo.country))
BEGIN
RAISERROR ('counrtyid not exist', 16, 1);
END;
ELSE
INSERT lab152.dbo.ruler (namer, ruler_country) SELECT namer, ruler_country FROM inserted;
END;
GO
CREATE TRIGGER updateruler ON lab152.dbo.ruler INSTEAD OF UPDATE
AS
IF (UPDATE(namer))
BEGIN
RAISERROR ('prohibited', 16, 1);
END
ELSE
BEGIN
IF EXISTS (SELECT ruler_country FROM inserted WHERE ruler_country NOT IN (SELECT lab151.dbo.country.countryid FROM lab151.dbo.country))
BEGIN
RAISERROR ('counrtyid not exist', 16, 1);
END;
ELSE
UPDATE lab152.dbo.ruler SET ruler_country = b.ruler_country FROM deleted a INNER JOIN inserted b ON a.namer =b.namer WHERE a.rulerid = lab152.dbo.ruler.rulerid;
END;
GO
USE MASTER;
GO
INSERT lab151.dbo.country (namec) VALUES ('Rome'), ('Greece'), ('Egypt');
INSERT lab152.dbo.ruler (namer, ruler_country) VALUES ('Octavian', 1), ('Caesar', 1), ('Raricles', 2), ('Ramses', 3);
GO
SELECT * FROM lab151.dbo.country;
SELECT * FROM lab152.dbo.ruler;
GO
UPDATE lab152.dbo.ruler SET ruler_country += 1 WHERE --ruler_country < 3;
namer like 'Ra%'
GO
SELECT * FROM lab151.dbo.country;
SELECT * FROM lab152.dbo.ruler;
GO
DELETE lab151.dbo.country WHERE countryid = 3;
GO
UPDATE lab151.dbo.country SET countryid = 4 WHERE countryid = 2
GO
SELECT * FROM lab151.dbo.country;
SELECT * FROM lab152.dbo.ruler;
GO
DROP TABLE lab152.dbo.ruler;
GO
DROP TABLE lab151.dbo.country;
GO