-- таблица страны CREATE TABLE country countryid int IDENTITY Primary

 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
-- таблица страны
CREATE TABLE country (
countryid int IDENTITY(1,1) Primary key NOT NULL,
namec nvarchar(30) NOT NULL,
unique_discription text NOT NULL DEFAULT 'описание отсутствует',
--flag image NOT NULL DEFAULT 'f'
);
GO
--связаная со страной таблица правителей
CREATE TABLE ruler (
rulerid int IDENTITY(1, 1) PRIMARY KEY NOT NULL,
namer nvarchar(30) NOT NULL,
Feature_I nvarchar(30) NOT NULL DEFAULT 'Черта отсутствует',
Feature_II nvarchar(30) NOT NULL DEFAULT 'Черта отсутствует',
ruler_country int FOREIGN KEY REFERENCES country(countryid));
GO
--промежуточная таблица в которой ключи страны
CREATE TABLE CT(
technologyid int NOT NULL FOREIGN KEY REFERENCES technology(technologyid),
countryid int NOT NULL FOREIGN KEY REFERENCES country(countryid),
PRIMARY KEY (technologyid, countryid),
);
GO
-- Представление на страну и правителя
CREATE VIEW Civilization WITH SCHEMABINDING AS
SELECT a.namec, a.unique_discription, b.namer, b.Feature_I, b.Feature_II FROM
dbo.country a INNER JOIN dbo.ruler b ON a.countryid = b.ruler_country
GO
-- триггер на представление
CREATE TRIGGER delete_Civilization ON Civilization INSTEAD OF DELETE
AS
DELETE FROM ruler WHERE namer IN (SELECT namer FROM deleted);
DELETE FROM CT WHERE countryid NOT IN (SELECT a.countryid FROM country a INNER JOIN deleted b ON a.namec = b.namec);
DELETE FROM country WHERE countryid NOT IN (SELECT ruler_country FROM ruler);
GO
--триггер на страну
CREATE TRIGGER delete_country ON country AFTER DELETE
AS
DELETE FROM CT WHERE countryid NOT IN (SELECT countryid FROM country);
DELETE FROM ruler WHERE ruler_country NOT IN (SELECT countryid FROM country);
GO
Запрос
DELETE FROM Civilization
GO
ошибка: Конфликт инструкции DELETE с ограничением REFERENCE "FK__CT__countryid__38996AB5". Конфликт произошел в базе данных "Civilizations", таблица "dbo.CT", column 'countryid'.