7) Триггеры

Kassa:

1. If @@Error>0

Begin

Raiserror('Ошибка',16,1)

RollBack Tran

Return

End

alter trigger Insert_kassa on kassa for insert

as

IF @@RowCount=0

Return

If @@RowCount>1

Begin

Raiserror('Ошибка',16,1)

RollBack Tran

Return

End

declare @dat datetime

select @dat=data_otp from Inserted

declare @nom_reis int

select @nom_reis=N_reis from inserted

declare @svobodn int

select @svobodn=svobod_mest from spravka s

join kassa k on k.N_reis=s.N_reis

where s.N_reis=@nom_reis and s.data_otp=@dat

if @svobodn=0

Begin

Raiserror('Свободных мест нет',16,1)

RollBack Tran

Return

End

If @@Error>0

Begin

Raiserror('Ошибка',16,1)

RollBack Tran

Return

End

2. alter trigger Insert_kassa2 on kassa for insert

as

IF @@RowCount=0

Return

If @@RowCount>1

Begin

Raiserror('Ошибка',16,1)

RollBack Tran

Return

End

declare @dat datetime

select @dat=data_otp from Inserted

declare @cena int

select @cena=cena from inserted

declare @nom_reis int

select @nom_reis=N_reis from inserted

declare @svobodn int

select @svobodn=svobod_mest from spravka s

join kassa k on k.N_reis=s.N_reis

where s.N_reis=@nom_reis and s.data_otp=@dat

declare @prodan int

select @prodan=kolvo_prod_mest from vipolneno v

join kassa k on k.N_reis=v.N_reis

where v.N_reis=@nom_reis and v.data_otp=@dat

update spravka

set svobod_mest=svobod_mest-1

where N_reis=@nom_reis and data_otp=@dat

update vipolneno

set kolvo_prod_mest=kolvo_prod_mest+1

where N_reis=@nom_reis and data_otp=@dat

update vipolneno

set summa=summa+@cena

where N_reis=@nom_reis and data_otp=@dat

If @@Error>0

Begin

Raiserror('Ошибка',16,1)

RollBack Tran

Return

End

3. create trigger Insert_kassa3 on kassa for insert

as

IF @@RowCount=0

Return

If @@RowCount>1

Begin

Raiserror('Ошибка',16,1)

RollBack Tran

Return

End

declare @dat datetime

select @dat=data_otp from Inserted

declare @nom_reis int

select @nom_reis=N_reis from inserted

If exists (select * from otmena o

join kassa k on k.N_reis=o.N_reis where o.n_reis=@nom_reis and

data=@dat)

Begin

Raiserror('Рейс отменен',16,1)

RollBack Tran

Return

End

19