Z5ejxzHh

1
2use dogsDatabase1;
3create table MutatedDogs(
4	id int IDENTITY(1,1),
5	name varchar(20),
6	pawCount int check(pawCount > 0)
7)
8use dogsDatabase1;
9insert into MutatedDogs values('MDog', 2);
10select * from MutatedDogs;
11
12
13use dogsDatabase2;
14create table UranBowl (
15	id int IDENTITY(1,1),
16	mdogId int not null,
17	radiationDose int not null
18)
19
20use dogsDatabase2;
21insert into UranBowl values(3, 200);
22go;
23
24select * from  dogsDatabase1.dbo.MutatedDogs
25
26-- cascade delete
27create trigger deleteMutatedDogTrigger
28	on MutatedDogs
29	after delete
30	as begin
31		delete dogsDatabase2.dbo.UranBowl
32		where mdogId in (select id from deleted)
33	end
34go;
35
36drop trigger deleteMutatedDogTrigger
37
38use dogsDatabase1;
39select * from MutatedDogs
40select * from dogsDatabase2.dbo.UranBowl;
41delete dbo.MutatedDogs 
42	where id = 3
43
44go;
45
46create trigger insertUranBowlTrigger
47	on dogsDatabase2.dbo.UranBowl
48	instead of insert
49	as 
50	begin
51		if (exists (
52			select mdogId from inserted
53			except 
54			select id from dogsDatabase1.dbo.MutatedDogs
55		))
56			THROW 666666, 'Foreign key exception', 1 
57		else begin
58			insert into UranBowl select mdogId, radiationDose from inserted
59		end
60
61	end
62go;
63
64use dogsDatabase2;
65insert into UranBowl(mdogId, radiationDose) values
66	(4, 300)
67
68select * from UranBowl;
69
70go;
71
72create trigger updateUranBowlTrigger
73	on dogsDatabase2.dbo.UranBowl
74	instead of update
75	as 
76	begin
77		if (exists (
78			select mdogId from inserted
79			except 
80			select id from dogsDatabase1.dbo.MutatedDogs
81		))
82			THROW 666666, 'Foreign key exception', 1 
83		else begin
84			update UranBowl set mdogId = a.mdogId from inserted a
85			update UranBowl set radiationDose = a.radiationDose from inserted a
86		end
87
88	end
89go;