Π‘Ρ
Π΅ΠΌΠ° Π΄Π°Π½Π½ΡΡ
.
ΠΠ½ΡΠΎΡΠΌΠ°ΡΠΈΠΎΠ½Π½Π°Ρ ΡΠΈΡΡΠ΅ΠΌΠ° Π²ΠΎΠ΅Π½Π½ΠΎΠ³ΠΎ ΠΎΠΊΡΡΠ³Π°
Raiserror 50 003 'Children still exist in table ''Voorugenie''. Cannot delete from parent table ''Podrazdelenie''.'. Raiserror 50 003 'Children still exist in table ''Soorugenia''. Cannot delete from parent table ''Podrazdelenie''.'. Raiserror 50 003 'Children still exist in table ''Tehnika''. Cannot delete from parent table ''Podrazdelenie''.'. Raiserror 50 003 'Children still exist in table… Π§ΠΈΡΠ°ΡΡ Π΅ΡΡ >
Π‘Ρ Π΅ΠΌΠ° Π΄Π°Π½Π½ΡΡ . ΠΠ½ΡΠΎΡΠΌΠ°ΡΠΈΠΎΠ½Π½Π°Ρ ΡΠΈΡΡΠ΅ΠΌΠ° Π²ΠΎΠ΅Π½Π½ΠΎΠ³ΠΎ ΠΎΠΊΡΡΠ³Π° (ΡΠ΅ΡΠ΅ΡΠ°Ρ, ΠΊΡΡΡΠΎΠ²Π°Ρ, Π΄ΠΈΠΏΠ»ΠΎΠΌ, ΠΊΠΎΠ½ΡΡΠΎΠ»ΡΠ½Π°Ρ)
Π‘ΠΊΡΠΈΠΏΡ
/*.
Created11.12.2006.
Modified19.12.2006.
Project.
Model.
Company.
Author.
Version.
DatabaseMS SQL 2000.
*/.
Drop trigger [tu_Chast].
go.
Drop trigger [tu_Rota].
go.
Drop trigger [tu_Vzvod].
go.
Drop trigger [tu_Obedinenie].
go.
Drop trigger [tu_armia].
go.
Drop trigger [tu_Podrazdelenie].
go.
Drop trigger [td_Chast].
go.
Drop trigger [td_Rota].
go.
Drop trigger [td_Vzvod].
go.
Drop trigger [td_Obedinenie].
go.
Drop trigger [td_armia].
go.
Drop trigger [td_Podrazdelenie].
go.
Drop table [Voorugenie].
go.
Drop table [Podrazdelenie].
go.
Drop table [Tehnika].
go.
Drop table [Soorugenia].
go.
Drop table [armia].
go.
Drop table [sostav].
go.
Drop table [Obedinenie].
go.
Drop table [Otdelenie].
go.
Drop table [Vzvod].
go.
Drop table [Rota].
go.
Drop table [Chast].
go.
Create table [Chast].
(.
[Nazvanie_chasti] Char (10) NULL,.
[Nomer_podrazdelenia] Char (10) NOT NULL,.
[tip_i_nomer_obedinenia] Char (10) NOT NULL,.
[nazvanie_armii] Char (10) NOT NULL,.
[Mesto] Char (10) NOT NULL,.
Constraint [pk_Chast] Primary Key ([Nomer_podrazdelenia],[tip_i_nomer_obedinenia],[nazvanie_armii],[Mesto]).
).
go.
Create table [Rota].
(.
[Nazv_roti] Char (10) NOT NULL,.
[Nomer_podrazdelenia] Char (10) NOT NULL,.
[tip_i_nomer_obedinenia] Char (10) NOT NULL,.
[nazvanie_armii] Char (10) NOT NULL,.
[Mesto] Char (10) NOT NULL,.
Constraint [pk_Rota] Primary Key ([Nazv_roti],[Nomer_podrazdelenia],[tip_i_nomer_obedinenia],[nazvanie_armii],[Mesto]).
).
go.
Create table [Vzvod].
(.
[Nazv_vzvoda] Char (10) NOT NULL,.
[Nomer_podrazdelenia] Char (10) NOT NULL,.
[nazvanie_armii] Char (10) NOT NULL,.
[tip_i_nomer_obedinenia] Char (10) NOT NULL,.
[Nazv_roti] Char (10) NOT NULL,.
[Mesto] Char (10) NOT NULL,.
Constraint [pk_Vzvod] Primary Key ([Nazv_vzvoda],[Nomer_podrazdelenia],[nazvanie_armii],[tip_i_nomer_obedinenia],[Nazv_roti],[Mesto]).
).
go.
Create table [Otdelenie].
(.
[Nazv_otdel] Char (10) NOT NULL,.
[Nomer_podrazdelenia] Char (10) NOT NULL,.
[nazvanie_armii] Char (10) NOT NULL,.
[tip_i_nomer_obedinenia] Char (10) NOT NULL,.
[Nazv_roti] Char (10) NOT NULL,.
[Nazv_vzvoda] Char (10) NOT NULL,.
[Mesto] Char (10) NOT NULL,.
Constraint [pk_Otdelenie] Primary Key ([Nazv_otdel],[Nomer_podrazdelenia],[nazvanie_armii],[tip_i_nomer_obedinenia],[Nazv_roti],[Nazv_vzvoda],[Mesto]).
).
go.
Create table [Obedinenie].
(.
[tip_i_nomer_obedinenia] Char (10) NOT NULL,.
[Nomer_podrazdelenia] Char (10) NOT NULL,.
[nazvanie_armii] Char (10) NOT NULL,.
Constraint [pk_Obedinenie] Primary Key ([tip_i_nomer_obedinenia],[Nomer_podrazdelenia],[nazvanie_armii]).
).
go.
Create table [sostav].
(.
[Kod_slugashego] Char (10) NOT NULL,.
[FIO] Char (30) NOT NULL,.
[Zvanie] Char (30) NOT NULL,.
[Special] Char (30) NOT NULL,.
[Tip_sostava] Char (10) NOT NULL,.
[Podchinaetca] Char (10) NOT NULL,.
[Nomer_podrazdelenia] Char (10) NOT NULL,.
Constraint [pk_sostav] Primary Key ([Kod_slugashego],[FIO],[Zvanie],[Nomer_podrazdelenia]).
).
go.
Create table [armia].
(.
[nazvanie_armii] Char (10) NOT NULL,.
[Nomer_podrazdelenia] Char (10) NOT NULL,.
Constraint [pk_armia] Primary Key ([nazvanie_armii],[Nomer_podrazdelenia]).
).
go.
Create table [Soorugenia].
(.
[Nomer_coorugenia] Char (10) NOT NULL,.
[Nomer_disloc_obedinenia] Char (10) NOT NULL,.
[Nomer_podrazdelenia] Char (10) NOT NULL,.
Constraint [pk_Soorugenia] Primary Key ([Nomer_coorugenia],[Nomer_disloc_obedinenia],[Nomer_podrazdelenia]).
).
go.
Create table [Tehnika].
(.
[Tip_tehniki] Char (10) NOT NULL,.
[Kol_vo_tehniki] Integer NOT NULL,.
[Nomer_podrazdelenia] Char (10) NOT NULL,.
Constraint [pk_Tehnika] Primary Key ([Tip_tehniki],[Kol_vo_tehniki],[Nomer_podrazdelenia]).
).
go.
Create table [Podrazdelenie].
(.
[Nomer_podrazdelenia] Char (10) NOT NULL,.
Constraint [pk_Podrazdelenie] Primary Key ([Nomer_podrazdelenia])).
go.
Create table [Voorugenie].
(.
[Tip_voorug] Char (10) NOT NULL,.
[Kol_vo_voorug] Integer NOT NULL,.
[Nomer_podrazdelenia] Char (10) NOT NULL,.
Constraint [pk_Voorugenie] Primary Key ([Tip_voorug],[Kol_vo_voorug],[Nomer_podrazdelenia])).
go.
Set quoted_identifier on.
go.
/* Update trigger «tu_Chast» for table «Chast» */.
Create trigger [tu_Chast].
on [Chast] for update as.
begin.
declare @numrows int.
select @numrows = @@rowcount.
if @numrows = 0.
return.
/* Restrict child «Rota» when parent «Chast» updated */.
if update ([Nomer_podrazdelenia]) or.
update ([tip_i_nomer_obedinenia]) or.
update ([nazvanie_armii]) or.
update ([Mesto]).
begin.
if exists (select 1 from [Rota] t, deleted d.
where t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia] and.
t. tip_i_nomer_obedinenia] = d. tip_i_nomer_obedinenia] and.
t. nazvanie_armii] = d. nazvanie_armii] and.
t. Mesto] = d. Mesto]).
begin.
raiserror 50 001 'Children still exist in table ''Rota''. Cannot update parent table ''Chast''.'.
rollback transaction.
return.
end.
end.
end.
go.
/* Update trigger «tu_Rota» for table «Rota» */.
Create trigger [tu_Rota].
on [Rota] for update as.
begin.
declare @numrows int.
select @numrows = @@rowcount.
if @numrows = 0.
return.
/* Restrict child «Vzvod» when parent «Rota» updated */.
if update ([Nazv_roti]) or.
update ([Nomer_podrazdelenia]) or.
update ([tip_i_nomer_obedinenia]) or.
update ([nazvanie_armii]) or.
update ([Mesto]).
begin.
if exists (select 1 from [Vzvod] t, deleted d.
where t. Nazv_roti] = d. Nazv_roti] and.
t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia] and.
t. tip_i_nomer_obedinenia] = d. tip_i_nomer_obedinenia] and.
t. nazvanie_armii] = d. nazvanie_armii] and.
t. Mesto] = d. Mesto]).
begin.
raiserror 50 001 'Children still exist in table ''Vzvod''. Cannot update parent table ''Rota''.'.
rollback transaction.
return.
end.
end.
end.
go.
/* Update trigger «tu_Vzvod» for table «Vzvod» */.
Create trigger [tu_Vzvod].
on [Vzvod] for update as.
begin.
declare @numrows int.
select @numrows = @@rowcount.
if @numrows = 0.
return.
/* Restrict child «Otdelenie» when parent «Vzvod» updated */.
if update ([Nazv_vzvoda]) or.
update ([Nomer_podrazdelenia]) or.
update ([nazvanie_armii]) or.
update ([tip_i_nomer_obedinenia]) or.
update ([Nazv_roti]) or.
update ([Mesto]).
begin.
if exists (select 1 from [Otdelenie] t, deleted d.
where t. Nazv_vzvoda] = d. Nazv_vzvoda] and.
t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia] and.
t. nazvanie_armii] = d. nazvanie_armii] and.
t. tip_i_nomer_obedinenia] = d. tip_i_nomer_obedinenia] and.
t. Nazv_roti] = d. Nazv_roti] and.
t. Mesto] = d. Mesto]).
begin.
raiserror 50 001 'Children still exist in table ''Otdelenie''. Cannot update parent table ''Vzvod''.'.
rollback transaction.
return.
end.
end.
end.
go.
/* Update trigger «tu_Obedinenie» for table «Obedinenie» */.
Create trigger [tu_Obedinenie].
on [Obedinenie] for update as.
begin.
declare @numrows int.
select @numrows = @@rowcount.
if @numrows = 0.
return.
/* Restrict child «Chast» when parent «Obedinenie» updated */.
if update ([tip_i_nomer_obedinenia]) or.
update ([Nomer_podrazdelenia]) or.
update ([nazvanie_armii]).
begin.
if exists (select 1 from [Chast] t, deleted d.
where t. tip_i_nomer_obedinenia] = d. tip_i_nomer_obedinenia] and.
t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia] and.
t. nazvanie_armii] = d. nazvanie_armii]).
begin.
raiserror 50 001 'Children still exist in table ''Chast''. Cannot update parent table ''Obedinenie''.'.
rollback transaction.
return.
end.
end.
end.
go.
/* Update trigger «tu_armia» for table «armia» */.
Create trigger [tu_armia].
on [armia] for update as.
begin.
declare @numrows int.
select @numrows = @@rowcount.
if @numrows = 0.
return.
/* Restrict child «Obedinenie» when parent «armia» updated */.
if update ([nazvanie_armii]) or.
update ([Nomer_podrazdelenia]).
begin.
if exists (select 1 from [Obedinenie] t, deleted d.
where t. nazvanie_armii] = d. nazvanie_armii] and.
t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia]).
begin.
raiserror 50 001 'Children still exist in table ''Obedinenie''. Cannot update parent table ''armia''.'.
rollback transaction.
return.
end.
end.
end.
go.
/* Update trigger «tu_Podrazdelenie» for table «Podrazdelenie» */.
Create trigger [tu_Podrazdelenie].
on [Podrazdelenie] for update as.
begin.
declare @numrows int.
select @numrows = @@rowcount.
if @numrows = 0.
return.
/* Restrict child «armia» when parent «Podrazdelenie» updated */.
if update ([Nomer_podrazdelenia]).
begin.
if exists (select 1 from [armia] t, deleted d.
where t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia]).
begin.
raiserror 50 001 'Children still exist in table ''armia''. Cannot update parent table ''Podrazdelenie''.'.
rollback transaction.
return.
end.
end.
/* Restrict child «sostav» when parent «Podrazdelenie» updated */.
if update ([Nomer_podrazdelenia]).
begin.
if exists (select 1 from [sostav] t, deleted d.
where t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia]).
begin.
raiserror 50 001 'Children still exist in table ''sostav''. Cannot update parent table ''Podrazdelenie''.'.
rollback transaction.
return.
end.
end.
/* Restrict child «Tehnika» when parent «Podrazdelenie» updated */.
if update ([Nomer_podrazdelenia]).
begin.
if exists (select 1 from [Tehnika] t, deleted d.
where t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia]).
begin.
raiserror 50 001 'Children still exist in table ''Tehnika''. Cannot update parent table ''Podrazdelenie''.'.
rollback transaction.
return.
end.
end.
/* Restrict child «Voorugenie» when parent «Podrazdelenie» updated */.
if update ([Nomer_podrazdelenia]).
begin.
if exists (select 1 from [Voorugenie] t, deleted d.
where t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia]).
begin.
raiserror 50 001 'Children still exist in table ''Voorugenie''. Cannot update parent table ''Podrazdelenie''.'.
rollback transaction.
return.
end.
end.
/* Restrict child «Soorugenia» when parent «Podrazdelenie» updated */.
if update ([Nomer_podrazdelenia]).
begin.
if exists (select 1 from [Soorugenia] t, deleted d.
where t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia]).
begin.
raiserror 50 001 'Children still exist in table ''Soorugenia''. Cannot update parent table ''Podrazdelenie''.'.
rollback transaction.
return.
end.
end.
end.
go.
/* Delete trigger «td_Chast» for table «Chast» */.
Create trigger [td_Chast].
on [Chast] for delete as.
begin.
declare @numrows int.
select @numrows = @@rowcount.
if @numrows = 0.
return.
/* Restrict child «Rota» when parent «Chast» deleted */.
if exists (select 1 from [Rota] t, deleted d.
where t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia] and.
t. tip_i_nomer_obedinenia] = d. tip_i_nomer_obedinenia] and.
t. nazvanie_armii] = d. nazvanie_armii] and.
t. Mesto] = d. Mesto]).
begin.
raiserror 50 003 'Children still exist in table ''Rota''. Cannot delete from parent table ''Chast''.'.
rollback transaction.
return.
end.
end.
go.
/* Delete trigger «td_Rota» for table «Rota» */.
Create trigger [td_Rota].
on [Rota] for delete as.
begin.
declare @numrows int.
select @numrows = @@rowcount.
if @numrows = 0.
return.
/* Restrict child «Vzvod» when parent «Rota» deleted */.
if exists (select 1 from [Vzvod] t, deleted d.
where t. Nazv_roti] = d. Nazv_roti] and.
t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia] and.
t. tip_i_nomer_obedinenia] = d. tip_i_nomer_obedinenia] and.
t. nazvanie_armii] = d. nazvanie_armii] and.
t. Mesto] = d. Mesto]).
begin.
raiserror 50 003 'Children still exist in table ''Vzvod''. Cannot delete from parent table ''Rota''.'.
rollback transaction.
return.
end.
end.
go.
/* Delete trigger «td_Vzvod» for table «Vzvod» */.
Create trigger [td_Vzvod].
on [Vzvod] for delete as.
begin.
declare @numrows int.
select @numrows = @@rowcount.
if @numrows = 0.
return.
/* Restrict child «Otdelenie» when parent «Vzvod» deleted */.
if exists (select 1 from [Otdelenie] t, deleted d.
where t. Nazv_vzvoda] = d. Nazv_vzvoda] and.
t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia] and.
t. nazvanie_armii] = d. nazvanie_armii] and.
t. tip_i_nomer_obedinenia] = d. tip_i_nomer_obedinenia] and.
t. Nazv_roti] = d. Nazv_roti] and.
t. Mesto] = d. Mesto]).
begin.
raiserror 50 003 'Children still exist in table ''Otdelenie''. Cannot delete from parent table ''Vzvod''.'.
rollback transaction.
return.
end.
end.
go.
/* Delete trigger «td_Obedinenie» for table «Obedinenie» */.
Create trigger [td_Obedinenie].
on [Obedinenie] for delete as.
begin.
declare @numrows int.
select @numrows = @@rowcount.
if @numrows = 0.
return.
/* Restrict child «Chast» when parent «Obedinenie» deleted */.
if exists (select 1 from [Chast] t, deleted d.
where t. tip_i_nomer_obedinenia] = d. tip_i_nomer_obedinenia] and.
t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia] and.
t. nazvanie_armii] = d. nazvanie_armii]).
begin.
raiserror 50 003 'Children still exist in table ''Chast''. Cannot delete from parent table ''Obedinenie''.'.
rollback transaction.
return.
end.
end.
go.
/* Delete trigger «td_armia» for table «armia» */.
Create trigger [td_armia].
on [armia] for delete as.
begin.
declare @numrows int.
select @numrows = @@rowcount.
if @numrows = 0.
return.
/* Restrict child «Obedinenie» when parent «armia» deleted */.
if exists (select 1 from [Obedinenie] t, deleted d.
where t. nazvanie_armii] = d. nazvanie_armii] and.
t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia]).
begin.
raiserror 50 003 'Children still exist in table ''Obedinenie''. Cannot delete from parent table ''armia''.'.
rollback transaction.
return.
end.
end.
go.
/* Delete trigger «td_Podrazdelenie» for table «Podrazdelenie» */.
Create trigger [td_Podrazdelenie].
on [Podrazdelenie] for delete as.
begin.
declare @numrows int.
select @numrows = @@rowcount.
if @numrows = 0.
return.
/* Restrict child «armia» when parent «Podrazdelenie» deleted */.
if exists (select 1 from [armia] t, deleted d.
where t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia]).
begin.
raiserror 50 003 'Children still exist in table ''armia''. Cannot delete from parent table ''Podrazdelenie''.'.
rollback transaction.
return.
end.
/* Restrict child «sostav» when parent «Podrazdelenie» deleted */.
if exists (select 1 from [sostav] t, deleted d.
where t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia]).
begin.
raiserror 50 003 'Children still exist in table ''sostav''. Cannot delete from parent table ''Podrazdelenie''.'.
rollback transaction.
return.
end.
/* Restrict child «Tehnika» when parent «Podrazdelenie» deleted */.
if exists (select 1 from [Tehnika] t, deleted d.
where t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia]).
begin.
raiserror 50 003 'Children still exist in table ''Tehnika''. Cannot delete from parent table ''Podrazdelenie''.'.
rollback transaction.
return.
end.
/* Restrict child «Voorugenie» when parent «Podrazdelenie» deleted */.
if exists (select 1 from [Voorugenie] t, deleted d.
where t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia]).
begin.
raiserror 50 003 'Children still exist in table ''Voorugenie''. Cannot delete from parent table ''Podrazdelenie''.'.
rollback transaction.
return.
end.
/* Restrict child «Soorugenia» when parent «Podrazdelenie» deleted */.
if exists (select 1 from [Soorugenia] t, deleted d.
where t. Nomer_podrazdelenia] = d. Nomer_podrazdelenia]).
begin.
raiserror 50 003 'Children still exist in table ''Soorugenia''. Cannot delete from parent table ''Podrazdelenie''.'.
rollback transaction.
return.
end.
end.
go.
Set quoted_identifier off.
go.
/* Roles permissions */.