QrYvSkGh

1use master;
2go
3
4drop database if exists lab15_db_1;
5go
6
7create database lab15_db_1;
8go
9
10use lab15_db_1;
11go
12
13create table VisaCenter
14(
15    VisaCenterName varchar(30) primary key not null,
16    Address        varchar(50)             not null,
17    WorkTime       time                    not null default '10:00:00'
18);
19go
20
21
22create trigger Trigger_VisaCenter_insert
23    on lab15_db_1.dbo.VisaCenter
24    instead of insert
25    as
26begin
27    if exists(
28            select *
29            from lab15_db_1.dbo.VisaCenter as vc
30                     join inserted as i
31                          on vc.VisaCenterName = i.VisaCenterName
32        )
33        begin
34            raiserror ('ERROR: This VisaCenter already exists!', 16, 0);
35        end
36    else
37        begin
38            insert into lab15_db_1.dbo.VisaCenter
39            select *
40            from inserted
41
42            insert into lab15_db_2.dbo.Tariff (VisaCenterName)
43            select VisaCenterName
44            from inserted
45        end
46end;
47go
48
49
50create trigger Trigger_VisaCenter_update
51    on lab15_db_1.dbo.VisaCenter
52    instead of update
53    as
54begin
55    if update(VisaCenterName)
56        begin
57            if exists(
58                    select *
59                    from lab15_db_1.dbo.VisaCenter as vc
60                             join inserted as i
61                                  on vc.VisaCenterName = i.VisaCenterName
62                )
63                begin
64                    raiserror ('ERROR: This VisaCenter already exists!', 16, 0);
65                end
66            else
67                begin
68                    update vc
69                    set vc.VisaCenterName = i.VisaCenterName
70                    from lab15_db_1.dbo.VisaCenter as vc
71                             join inserted as i
72                                  on vc.VisaCenterName
73                                      in (
74                                         select VisaCenterName
75                                         from deleted
76                                     );
77
78                    update t
79                    set t.VisaCenterName = i.VisaCenterName
80                    from lab15_db_2.dbo.Tariff as t
81                             join inserted as i
82                                  on t.VisaCenterName
83                                      in (
84                                         select VisaCenterName
85                                         from deleted
86                                     );
87                end
88        end
89
90    if update(Address)
91        begin
92            update vc
93            set vc.Address = i.Address
94            from lab15_db_1.dbo.VisaCenter as vc
95                     join inserted as i
96                          on vc.VisaCenterName = i.VisaCenterName;
97        end
98
99    if update(WorkTime)
100        begin
101            update vc
102            set vc.WorkTime = i.WorkTime
103            from lab15_db_1.dbo.VisaCenter as vc
104                     join inserted as i
105                          on vc.VisaCenterName = i.VisaCenterName;
106        end
107end;
108go
109
110
111create trigger Trigger_VisaCenter_delete
112    on lab15_db_1.dbo.VisaCenter
113    instead of delete
114    as
115begin
116    delete
117    from lab15_db_1.dbo.VisaCenter
118    where VisaCenterName in (
119        select VisaCenterName
120        from deleted
121    )
122
123    if exists(
124            select *
125            from lab15_db_2.dbo.Tariff as t
126                     join deleted as d
127                          on t.VisaCenterName = d.VisaCenterName
128        )
129        begin
130            delete
131            from lab15_db_2.dbo.Tariff
132            where VisaCenterName in (
133                select VisaCenterName
134                from deleted
135            );
136        end
137end
138go