QHHigbEb

1use master;
2go
3
4drop database if exists lab15_db_2;
5go
6
7create database lab15_db_2;
8go
9
10use lab15_db_2;
11go
12
13create table Tariff
14(
15    VisaCenterName varchar(30) not null,
16    TariffName     varchar(30)   default 'edit tariff name',
17    primary key (VisaCenterName, TariffName),
18
19    Conditions     varchar(1000) default 'edit conditions',
20    Price          money         default 3000
21);
22go
23
24
25create trigger Trigger_Tariff_insert
26    on lab15_db_2.dbo.Tariff
27    instead of insert
28    as
29begin
30    if not exists(
31            select *
32            from lab15_db_1.dbo.VisaCenter as vc
33                     join inserted as i
34                          on vc.VisaCenterName = i.VisaCenterName
35        )
36        begin
37            raiserror ('This VisaCenter does not exist', 16, 1);
38        end
39    else
40        begin
41            insert into lab15_db_2.dbo.Tariff
42            select *
43            from inserted;
44        end
45end;
46go
47
48
49create trigger Trigger_Tariff_update
50    on lab15_db_2.dbo.Tariff
51    instead of update
52    as
53begin
54    if update(VisaCenterName)
55        begin
56            raiserror ('Prohibit to change a VisaCenterName', 16, 2);
57        end
58
59    if update(TariffName)
60    begin
61        update t
62        set TariffName = i.TariffName
63        from lab15_db_2.dbo.Tariff as t,
64             inserted as i
65        where t.VisaCenterName = i.VisaCenterName
66    end
67
68    if update(Conditions)
69    begin
70        update t
71        set Conditions = i.Conditions
72        from lab15_db_2.dbo.Tariff as t,
73             inserted as i
74        where t.VisaCenterName = i.VisaCenterName
75    end
76
77    if update(Price)
78    begin
79        update t
80        set Price = i.Price
81        from lab15_db_2.dbo.Tariff as t,
82             inserted as i
83        where t.VisaCenterName = i.VisaCenterName
84    end
85end;
86go