-- убираем здания, сидящие на одной адресной записе и добавляем unique
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AfterBuildingsUpdate]') and xtype = N'TR')
drop trigger [dbo].[AfterBuildingsUpdate]

select AddressID, COUNT(*) as cnt, min(BuildingID) as ToID
into #AddressTempTable
from Buildings 
where AddressID is not null
group by AddressID
delete from #AddressTempTable where cnt = 1

update ConsumingObjects set BuildingID = b.ToID
  from Buildings
  inner join #AddressTempTable b on b.AddressID = Buildings.AddressID
where ConsumingObjects.BuildingID = Buildings.BuildingID

delete from Constructions
  from Constructions cn
  inner join Buildings bld on cn.ConstructionID = bld.BuildingID
  inner join #AddressTempTable b on b.AddressID = bld.AddressID
  where bld.BuildingID <> b.ToID
GO
drop table #AddressTempTable	
GO
ALTER TABLE Buildings ADD CONSTRAINT DF_Buildings_AddressID UNIQUE (AddressID)
GO
CREATE TRIGGER AfterBuildingsUpdate ON [dbo].[Buildings] 
AFTER UPDATE,DELETE
AS
  SET NOCOUNT ON
  if (SELECT Count(AddressID) FROM DELETED) > 0
    DELETE FROM AddressBaseParts WHERE 
      AddressID in (select AddressID from DELETED)
      AND ((select count(AddressID) from inserted) = 0 OR   
      AddressID not in (select AddressID from INSERTED))  
  SET NOCOUNT OFF
GO
-- убираем дублирующиеся здания
select 
  IsNull(LocalityID,0) as LocalityID, 
  IsNull(StreetID,0) as StreetID, 
  IsNull(EstateType,0) as EstateType, 
  IsNull(Estate,'') as Estate,
  IsNull(Fraction,'') as Fraction, 
  IsNull(Building,'') as Building, 
  IsNull(Housing,'') as Housing, 
  IsNull(Note,'') as Note, 
  Count(*) as cnt, 
  MIN(BuildingID) as ToID
into #BuildingTempTable
from Buildings
inner join AddressBaseParts A on Buildings.AddressID = A.AddressID
group by 
  IsNull(LocalityID,0), 
  IsNull(StreetID,0), 
  IsNull(EstateType,0), 
  IsNull(Estate,''),
  IsNull(Fraction,''), 
  IsNull(Building,''), 
  IsNull(Housing,''), 
  IsNull(Note,'')

delete from #BuildingTempTable where cnt = 1

update ConsumingObjects set BuildingID = b.ToID
  from Buildings
  inner join AddressBaseParts A on Buildings.AddressID = A.AddressID
  inner join #BuildingTempTable b on
    IsNull(A.LocalityID,0) = IsNull(b.LocalityID,0) AND
    IsNull(A.StreetID,0) = IsNull(b.StreetID,0) AND
    IsNull(A.EstateType,0) = IsNull(b.EstateType,0) AND 
    IsNull(A.Estate, '') = IsNull(b.Estate, '') AND
    IsNull(A.Fraction, '') = IsNull(b.Fraction, '') AND
    IsNull(A.Building, '') = IsNull(b.Building, '') AND
    IsNull(A.Housing, '') = IsNull(b.Housing, '') AND
    IsNull(A.Note, '') = IsNull(b.Note, '')
where ConsumingObjects.BuildingID = Buildings.BuildingID

delete from Constructions
  from Constructions cn
  inner join Buildings bld on cn.ConstructionID = bld.BuildingID
  inner join AddressBaseParts A on bld.AddressID = A.AddressID
  inner join #BuildingTempTable b on
    IsNull(A.LocalityID,0) = IsNull(b.LocalityID,0) AND
    IsNull(A.StreetID,0) = IsNull(b.StreetID,0) AND
    IsNull(A.EstateType,0) = IsNull(b.EstateType,0) AND 
    IsNull(A.Estate, '') = IsNull(b.Estate, '') AND
    IsNull(A.Fraction, '') = IsNull(b.Fraction, '') AND
    IsNull(A.Building, '') = IsNull(b.Building, '') AND
    IsNull(A.Housing, '') = IsNull(b.Housing, '') AND
    IsNull(A.Note, '') = IsNull(b.Note, '')
  where bld.BuildingID <> b.ToID
GO
drop table #BuildingTempTable
GO
-- добавляем uniq для объектов, использующих Адрес - предварительно убираем дубли, которые могли возникнуть при конвертации
  -- ConsumingObjects
alter table AddressExtraParts add ObjID int
GO
select AddressID, COUNT(*) as cnt, min(ID) as ToID
into #AddressTempTable
from ConsumingObjects 
where AddressID is not null
group by AddressID

delete from #AddressTempTable where cnt = 1

insert into AddressExtraParts
  select a.Flat, a.Entrance, co.ID
  from ConsumingObjects co
  inner join AddressExtraParts a on a.ExtraPartID = co.AddressID
  inner join #AddressTempTable at on at.AddressID = a.ExtraPartID
  where co.ID <> at.ToID
GO  
drop table #AddressTempTable
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AfterConsumingObjectsUpdate]') and xtype = N'TR')
drop trigger [dbo].[AfterConsumingObjectsUpdate]

update ConsumingObjects set AddressID = ExtraPartID
  from  AddressExtraParts
  where ObjID = ID

alter table AddressExtraParts drop column ObjID
GO
ALTER TABLE ConsumingObjects ADD CONSTRAINT DF_ConsumingObjects_AddressID UNIQUE (AddressID)
GO
CREATE TRIGGER AfterConsumingObjectsUpdate ON [dbo].[ConsumingObjects] 
AFTER UPDATE, DELETE
AS
  SET NOCOUNT ON
  if (SELECT Count(AddressID) FROM DELETED) > 0
    DELETE FROM AddressExtraParts WHERE 
      ExtraPartID in (select AddressID from DELETED)
      AND ((select count(AddressID) from inserted) = 0 OR   
      ExtraPartID not in (select AddressID from INSERTED))  
  SET NOCOUNT OFF

GO
  -- Individuals - RegistrationAddressID - unique нельзя, потому что nullable, но просто уберём дублирующиеся ненулевые
alter table AddressExtraParts add ObjID int
alter table AddressBaseParts add ObjID int
alter table Addresses add ObjID int
GO
select RegistrationAddressID as AddressID, COUNT(*) as cnt, min(IndividualID) as ToID
into #AddressTempTable
from Individuals
where RegistrationAddressID is not null
group by RegistrationAddressID

delete from #AddressTempTable where cnt = 1

insert into AddressExtraParts
  select ae.Flat, ae.Entrance, obj.IndividualID
  from Individuals obj
  inner join Addresses a on a.AddressID = obj.RegistrationAddressID
  inner join AddressExtraParts ae on ae.ExtraPartID = a.ExtraPartID
  inner join #AddressTempTable at on at.AddressID = a.AddressID
  where obj.IndividualID <> at.ToID

insert into AddressBaseParts
  select 
    ab.LocalityID, 
    ab.StreetID, 
    ab.EstateType, 
    ab.Estate,
    ab.Fraction, 
    ab.Building, 
    ab.Housing, 
    ab.Note, 
    obj.IndividualID
  from Individuals obj
  inner join AddressBaseParts ab on ab.AddressID = obj.RegistrationAddressID
  inner join #AddressTempTable at on at.AddressID = ab.AddressID
  where obj.IndividualID <> at.ToID

insert into Addresses(AddressID, ExtraPartID, ObjID)
  select ab.AddressID, ae.ExtraPartID, ab.ObjID
  from AddressBaseParts ab, AddressExtraParts ae
  where ab.ObjID = ae.ObjID AND ab.ObjID is not null
GO
drop table #AddressTempTable
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AfterIndividualsUpdate]') and xtype = N'TR')
drop trigger [dbo].[AfterIndividualsUpdate]
GO
update Individuals set RegistrationAddressID = AddressID
  from  Addresses
  where ObjID = IndividualID
 
alter table AddressBaseParts drop column ObjID
alter table AddressExtraParts drop column ObjID
alter table Addresses drop column ObjID
GO
CREATE TRIGGER AfterIndividualsUpdate ON [dbo].[Individuals] 
AFTER UPDATE,DELETE
AS
  if (SELECT Count(RegistrationAddressID) FROM DELETED) > 0
    DELETE FROM AddressBaseParts WHERE 
      AddressID in (select RegistrationAddressID from DELETED)
      AND ((select count(RegistrationAddressID) from inserted) = 0 OR   
      AddressID not in (select RegistrationAddressID from INSERTED))  
GO
  -- Организация - Код_юридического_адреса - unique нельзя, потому что nullable, но просто уберём дублирующиеся ненулевые
  ---- всё нормально - проверок не ставим
  -- LegalEntities - unique нельзя, потому что nullable, но просто уберём дублирующиеся ненулевые
alter table AddressExtraParts add ObjID int
alter table AddressBaseParts add ObjID int
alter table Addresses add ObjID int
GO
select AddressID as AddressID, COUNT(*) as cnt, min(LegalEntityID) as ToID
into #AddressTempTable
from LegalEntities
where AddressID is not null
group by AddressID

delete from #AddressTempTable where cnt = 1

insert into AddressExtraParts
  select ae.Flat, ae.Entrance, obj.LegalEntityID
  from LegalEntities obj
  inner join Addresses a on a.AddressID = obj.AddressID
  inner join AddressExtraParts ae on ae.ExtraPartID = a.ExtraPartID
  inner join #AddressTempTable at on at.AddressID = a.AddressID
  where obj.LegalEntityID <> at.ToID

insert into AddressBaseParts
  select 
    ab.LocalityID, 
    ab.StreetID, 
    ab.EstateType, 
    ab.Estate,
    ab.Fraction, 
    ab.Building, 
    ab.Housing, 
    ab.Note, 
    obj.LegalEntityID
  from LegalEntities obj
  inner join AddressBaseParts ab on ab.AddressID = obj.AddressID
  inner join #AddressTempTable at on at.AddressID = ab.AddressID
  where obj.LegalEntityID <> at.ToID

insert into Addresses(AddressID, ExtraPartID, ObjID)
  select ab.AddressID, ae.ExtraPartID, ab.ObjID
  from AddressBaseParts ab, AddressExtraParts ae
  where ab.ObjID = ae.ObjID AND ab.ObjID is not null

GO
drop table #AddressTempTable
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AfterLegalEntitiesUpdate]') and xtype = N'TR')
drop trigger [dbo].[AfterLegalEntitiesUpdate]
GO
update LegalEntities set AddressID = a.AddressID
  from  Addresses a
  where ObjID = LegalEntityID
 
alter table AddressBaseParts drop column ObjID
alter table AddressExtraParts drop column ObjID
alter table Addresses drop column ObjID
GO
CREATE TRIGGER AfterLegalEntitiesUpdate ON [dbo].[LegalEntities] 
AFTER UPDATE,DELETE
AS
  if (SELECT Count(AddressID) FROM DELETED) > 0
    DELETE FROM AddressBaseParts WHERE 
      AddressID in (select AddressID from DELETED)
      AND ((select count(AddressID) from inserted) = 0 OR   
      AddressID not in (select AddressID from INSERTED))  
  -- Линия - ок
  -- Группа_ячейки - ок
  -- Подстанция - ок
  -- Склад - ок
GO
-- добавляем триггер на здания с одинаковым адресом
CREATE TRIGGER OnBuildingsUpdate ON [dbo].[Buildings] 
FOR INSERT, UPDATE
AS
  if exists(
    select 1 
    from Buildings
    inner join inserted on Buildings.BuildingID <> inserted.BuildingID 
    inner join AddressBaseParts A on A.AddressID = Buildings.AddressID
    inner join AddressBaseParts b on b.AddressID = inserted.AddressID    
    where
      IsNull(A.LocalityID,0) = IsNull(b.LocalityID,0) AND
      IsNull(A.StreetID,0) = IsNull(b.StreetID,0) AND
      IsNull(A.EstateType,0) = IsNull(b.EstateType,0) AND 
      IsNull(A.Estate, '') = IsNull(b.Estate, '') AND
      IsNull(A.Fraction, '') = IsNull(b.Fraction, '') AND
      IsNull(A.Building, '') = IsNull(b.Building, '') AND
      IsNull(A.Housing, '') = IsNull(b.Housing, '') AND
      IsNull(A.Note, '') = IsNull(b.Note, ''))
  begin
    RAISERROR ('Ошибка при попытке установить адрес в таблицу Buildings. Здание с полностью идентичным адресом уже существует!',16,1)
    ROLLBACK TRANSACTION
  end
GO  
-- добавим uniq на ConstructionID в Строительном_сооружении
ALTER TABLE Строительное_сооружение ADD CONSTRAINT DF_Строительное_сооружение_ConstructionID UNIQUE (ConstructionID)
GO
-- очередные забытые связи
ALTER TABLE DelineationItems
	ADD FOREIGN KEY (RefID)
	REFERENCES Объект(Код_объекта)
	ON DELETE CASCADE

ALTER TABLE DelineationItems
	ADD FOREIGN KEY (PseudoRefID)
	REFERENCES PseudoObjects(PseudoObjectID)
	ON DELETE CASCADE

ALTER TABLE DelineationItems
	ADD FOREIGN KEY (DelineationID)
	REFERENCES Delineations(DelineationID)
	ON DELETE CASCADE
        ON UPDATE CASCADE
GO
-- триггеры на уникальность Адресов там, где из-за nullable нельзя сделать unique
CREATE TRIGGER OnIndividualsUpdate ON [dbo].[Individuals] 
FOR INSERT, UPDATE
AS
  if exists(
    select 1 
    from inserted
    inner join Individuals on Individuals.RegistrationAddressID = inserted.RegistrationAddressID 
    where inserted.RegistrationAddressID IS NOT NULL AND inserted.IndividualID <> Individuals.IndividualID)
  begin
    RAISERROR ('Значение поля RegistrationAddressID в таблице Individuals должно быть уникальным',16,1)
    ROLLBACK TRANSACTION
  end
GO
CREATE TRIGGER OnОрганизацияUpdate ON [dbo].[Организация] 
FOR INSERT, UPDATE
AS
  if exists(
    select 1 
    from inserted
    inner join Организация on Организация.Код_юридического_адреса = inserted.Код_юридического_адреса 
    where inserted.Код_юридического_адреса IS NOT NULL AND inserted.Код_организации <> Организация.Код_организации)
  begin
    RAISERROR ('Значение поля Код_юридического_адреса в таблице Организация должно быть уникальным',16,1)
    ROLLBACK TRANSACTION
  end
GO
CREATE TRIGGER OnLegalEntitiesUpdate ON [dbo].[LegalEntities] 
FOR INSERT, UPDATE
AS
  if exists(
    select 1 
    from inserted
    inner join LegalEntities on LegalEntities.AddressID = inserted.AddressID 
    where inserted.AddressID IS NOT NULL AND inserted.LegalEntityID <> LegalEntities.LegalEntityID)
  begin
    RAISERROR ('Значение поля AddressID в таблице LegalEntities должно быть уникальным',16,1)
    ROLLBACK TRANSACTION
  end
GO
CREATE TRIGGER OnГруппа_ячейкиUpdate ON [dbo].[Группа_ячейки] 
FOR INSERT, UPDATE
AS
  if exists(
    select 1 
    from inserted
    inner join Группа_ячейки on Группа_ячейки.AddressID = inserted.AddressID 
    where inserted.AddressID IS NOT NULL AND inserted.Код_группы <> Группа_ячейки.Код_группы)
  begin
    RAISERROR ('Значение поля AddressID в таблице Группа_ячейки должно быть уникальным',16,1)
    ROLLBACK TRANSACTION
  end
GO
CREATE TRIGGER OnЛинияUpdate ON [dbo].[Линия] 
FOR INSERT, UPDATE
AS
  if exists(
    select 1 
    from inserted
    inner join Линия on Линия.AddressID = inserted.AddressID 
    where inserted.AddressID IS NOT NULL AND inserted.Код_линии <> Линия.Код_линии)
  begin
    RAISERROR ('Значение поля AddressID в таблице Линия должно быть уникальным',16,1)
    ROLLBACK TRANSACTION
  end
GO
CREATE TRIGGER OnПодстанцияUpdate ON [dbo].[Подстанция] 
FOR INSERT, UPDATE
AS
  if exists(
    select 1 
    from inserted
    inner join Подстанция on Подстанция.AddressID = inserted.AddressID 
    where inserted.AddressID IS NOT NULL AND inserted.Код_подстанции <> Подстанция.Код_подстанции)
  begin
    RAISERROR ('Значение поля AddressID в таблице Подстанция должно быть уникальным',16,1)
    ROLLBACK TRANSACTION
  end
GO
CREATE TRIGGER OnСкладUpdate ON [dbo].[Склад] 
FOR INSERT, UPDATE
AS
  if exists(
    select 1 
    from inserted
    inner join Склад on Склад.AddressID = inserted.AddressID 
    where inserted.AddressID IS NOT NULL AND inserted.Код_склада <> Склад.Код_склада)
  begin
    RAISERROR ('Значение поля AddressID в таблице Склад должно быть уникальным',16,1)
    ROLLBACK TRANSACTION
  end