-- 1.1.0 Power.TechnologicalConnection

-- Добавим флаг обозначающий выполнение заявки.
ALTER TABLE TCApplications add IsExecuteComplite bit
-- Добавим число подъездов и число квартир
alter table Buildings add EntranceNum int
alter table Buildings add FlatNum int
GO
ALTER TRIGGER [dbo].[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
    declare @addressID int
    select @addressID = A.AddressID
    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, '')
    declare @message nvarchar(500)
    set @message = 'Ошибка при попытке установить адрес в таблицу Buildings. Здание с полностью идентичным адресом уже существует! (AddressID = ' + cast(@addressId as nvarchar)+')'     
    RAISERROR (@message,16,1)
    ROLLBACK TRANSACTION
  end