USE Civilizations GO IF OBJECT_ID insert_Civilization TR IS NOT NULL D

 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
USE Civilizations;
GO
IF OBJECT_ID ('insert_Civilization','TR') IS NOT NULL
DROP TRIGGER insert_Civilization;
GO
CREATE TRIGGER insert_Civilization ON Civilization INSTEAD OF INSERT
AS
BEGIN
INSERT country(namec) SELECT DISTINCT namec FROM inserted WHERE namec NOT IN (SELECT namec FROM country);
UPDATE country SET unique_discription =a.unique_discription FROM inserted a WHERE (country.namec = a.namec AND a.unique_discription IS NOT NULL)
INSERT ruler(ruler_country, namer) SELECT DISTINCT a.countryid, b.namer FROM dbo.country a INNER JOIN inserted b ON a.namec = b.namec WHERE b.namer NOT IN (SELECT namer FROM ruler);
UPDATE ruler SET Feature_I = a.Feature_I FROM inserted a WHERE (ruler.namer = a.namer AND a.Feature_I IS NOT NULL);
UPDATE ruler SET Feature_II = a.Feature_II FROM inserted a WHERE (ruler.namer = a.namer AND a.Feature_II IS NOT NULL);
END;
GO
IF OBJECT_ID ('delete_Civilization','TR') IS NOT NULL
DROP TRIGGER delete_Civilization;
GO
CREATE TRIGGER delete_Civilization ON Civilization INSTEAD OF DELETE
AS
DELETE FROM ruler WHERE namer IN (SELECT namer FROM deleted);
DELETE FROM country WHERE countryid NOT IN (SELECT ruler_country FROM ruler);
GO
IF OBJECT_ID ('update_Civilization','TR') IS NOT NULL
DROP TRIGGER update_Civilization;
GO
CREATE TRIGGER update_Civilization ON Civilization INSTEAD OF UPDATE
AS
IF (UPDATE(namec))
BEGIN
INSERT country(namec) SELECT DISTINCT namec FROM inserted WHERE namec NOT IN (SELECT namec FROM country);
UPDATE country SET unique_discription =a.unique_discription FROM inserted a WHERE (country.namec = a.namec AND a.unique_discription IS NOT NULL)
UPDATE ruler SET ruler_country = countryid FROM country a INNER JOIN inserted b ON a.namec =b.namec WHERE ruler.namer = b.namer;
DELETE FROM country WHERE countryid NOT IN (SELECT ruler_country FROM ruler);
END;
IF (UPDATE(unique_discription))
BEGIN
CREATE TABLE #table3 (id1 int IDENTITY PRIMARY KEY, new_name text);
CREATE TABLE #table4 (id2 int IDENTITY PRIMARY KEY, old_name nvarchar(30));
INSERT #table3 (new_name) SELECT unique_discription FROM inserted;
INSERT #table4 (old_name) SELECT DISTINCT namec FROM deleted;
UPDATE country SET unique_discription = new_name FROM #table3 INNER JOIN #table4 ON #table3.id1 = #table4.id2 WHERE (country.namec = #table4.old_name AND #table3.new_name IS NOT NULL);
DROP TABLE #table3;
DROP TABLE #table4;
END;
IF (UPDATE(namer))
BEGIN
CREATE TABLE #table5 (id1 int IDENTITY PRIMARY KEY, new_name nvarchar(30));
CREATE TABLE #table6 (id2 int IDENTITY PRIMARY KEY, old_name nvarchar(30));
INSERT #table5 (new_name) SELECT DISTINCT namer FROM inserted;
INSERT #table6 (old_name) SELECT DISTINCT namer FROM deleted;
UPDATE ruler SET namer = new_name FROM #table5 INNER JOIN #table6 ON #table5.id1 = #table6.id2 WHERE (ruler.namer = #table6.old_name AND #table5.new_name NOT IN (SELECT namer FROM ruler));
DROP TABLE #table5;
DROP TABLE #table6;
END;
IF (UPDATE(Feature_I))
BEGIN
CREATE TABLE #table7 (id1 int IDENTITY PRIMARY KEY, new_name nvarchar(30));
CREATE TABLE #table8 (id2 int IDENTITY PRIMARY KEY, old_name nvarchar(30));
INSERT #table7 (new_name) SELECT DISTINCT Feature_I FROM inserted;
INSERT #table8 (old_name) SELECT DISTINCT namer FROM deleted;
UPDATE ruler SET Feature_I = new_name FROM #table7 INNER JOIN #table8 ON #table7.id1 = #table8.id2 WHERE (ruler.namer = #table8.old_name AND #table7.new_name IS NOT NULL);
DROP TABLE #table7;
DROP TABLE #table8;
END;
IF (UPDATE(Feature_II))
BEGIN
CREATE TABLE #table9 (id1 int IDENTITY PRIMARY KEY, new_name nvarchar(30));
CREATE TABLE #table0 (id2 int IDENTITY PRIMARY KEY, old_name nvarchar(30));
INSERT #table9 (new_name) SELECT DISTINCT Feature_II FROM inserted;
INSERT #table0 (old_name) SELECT DISTINCT namer FROM deleted;
UPDATE ruler SET Feature_II = new_name FROM #table9 INNER JOIN #table0 ON #table9.id1 = #table0.id2 WHERE (ruler.namer = #table0.old_name AND #table9.new_name IS NOT NULL);
DROP TABLE #table9;
DROP TABLE #table0;
END;
GO