201955 (608122), страница 2
Текст из файла (страница 2)
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 50001 '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 50001 '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 50001 '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 50001 '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 50001 '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 50001 '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 50001 '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 50001 '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 50001 '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 50001 '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 50003 '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
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
