TR_CONTROLACCOUNT_INSUPD

CREATE trigger dbo.TR_CONTROLACCOUNT_INSUPD
  on dbo.CONTROLACCOUNT for insert, update
as
if
  (
    (substring(columns_updated(),1,1) & 224 > 0) or
    (substring(columns_updated(),2,1) & 255 > 0) or
    (substring(columns_updated(),3,1) & 255 > 0) or
    (substring(columns_updated(),4,1) & 255 > 0) or
    (substring(columns_updated(),5,1) & 7 > 0)
  )
begin

  -- Validate that the account doesn't violate any existing element relationships

  if exists (select 1 from dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL)
  begin
  declare @RULEID nvarchar(4000) = null
    begin try
      select @RULEID = coalesce(@RULEID + ', ', '') + AER.RELATIONSHIPID
      from inserted CA
      inner join dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL AERD on
        (AERD.DATAELEMENT1ID is null or AERD.DATAELEMENT1ID = CA.DATAELEMENT1ID)
        and
        (AERD.DATAELEMENT2ID is null or AERD.DATAELEMENT2ID = CA.DATAELEMENT2ID)
        and
        (AERD.DATAELEMENT3ID is null or AERD.DATAELEMENT3ID = CA.DATAELEMENT3ID)
        and
        (AERD.DATAELEMENT4ID is null or AERD.DATAELEMENT4ID = CA.DATAELEMENT4ID)
        and
        (AERD.DATAELEMENT5ID is null or AERD.DATAELEMENT5ID = CA.DATAELEMENT5ID)
        and
        (AERD.DATAELEMENT6ID is null or AERD.DATAELEMENT6ID = CA.DATAELEMENT6ID)
        and
        (AERD.DATAELEMENT7ID is null or AERD.DATAELEMENT7ID = CA.DATAELEMENT7ID)
        and
        (AERD.DATAELEMENT8ID is null or AERD.DATAELEMENT8ID = CA.DATAELEMENT8ID)
        and
        (AERD.DATAELEMENT9ID is null or AERD.DATAELEMENT9ID = CA.DATAELEMENT9ID)
        and
        (AERD.DATAELEMENT10ID is null or AERD.DATAELEMENT10ID = CA.DATAELEMENT10ID)
        and
        (AERD.DATAELEMENT11ID is null or AERD.DATAELEMENT11ID = CA.DATAELEMENT11ID)
        and
        (AERD.DATAELEMENT12ID is null or AERD.DATAELEMENT12ID = CA.DATAELEMENT12ID)
        and
        (AERD.DATAELEMENT13ID is null or AERD.DATAELEMENT13ID = CA.DATAELEMENT13ID)
        and
        (AERD.DATAELEMENT14ID is null or AERD.DATAELEMENT14ID = CA.DATAELEMENT14ID)
        and
        (AERD.DATAELEMENT15ID is null or AERD.DATAELEMENT15ID = CA.DATAELEMENT15ID)
        and
        (AERD.DATAELEMENT16ID is null or AERD.DATAELEMENT16ID = CA.DATAELEMENT16ID)
        and
        (AERD.DATAELEMENT17ID is null or AERD.DATAELEMENT17ID = CA.DATAELEMENT17ID)
        and
        (AERD.DATAELEMENT18ID is null or AERD.DATAELEMENT18ID = CA.DATAELEMENT18ID)
        and
        (AERD.DATAELEMENT19ID is null or AERD.DATAELEMENT19ID = CA.DATAELEMENT19ID)
        and
        (AERD.DATAELEMENT20ID is null or AERD.DATAELEMENT20ID = CA.DATAELEMENT20ID)
        and
        (AERD.DATAELEMENT21ID is null or AERD.DATAELEMENT21ID = CA.DATAELEMENT21ID)
        and
        (AERD.DATAELEMENT22ID is null or AERD.DATAELEMENT22ID = CA.DATAELEMENT22ID)
        and
        (AERD.DATAELEMENT23ID is null or AERD.DATAELEMENT23ID = CA.DATAELEMENT23ID)
        and
        (AERD.DATAELEMENT24ID is null or AERD.DATAELEMENT24ID = CA.DATAELEMENT24ID)
        and
        (AERD.DATAELEMENT25ID is null or AERD.DATAELEMENT25ID = CA.DATAELEMENT25ID)
        and
        (AERD.DATAELEMENT26ID is null or AERD.DATAELEMENT26ID = CA.DATAELEMENT26ID)
        and
        (AERD.DATAELEMENT27ID is null or AERD.DATAELEMENT27ID = CA.DATAELEMENT27ID)
        and
        (AERD.DATAELEMENT28ID is null or AERD.DATAELEMENT28ID = CA.DATAELEMENT28ID)
        and
        (AERD.DATAELEMENT29ID is null or AERD.DATAELEMENT29ID = CA.DATAELEMENT29ID)
        and
        (AERD.DATAELEMENT30ID is null or AERD.DATAELEMENT30ID = CA.DATAELEMENT30ID)
      inner join dbo.ACCOUNTINGELEMENTRELATIONSHIP AER on AERD.ACCOUNTINGELEMENTRELATIONSHIPID = AER.ID
      group by AER.ID, AER.RELATIONSHIPID
      declare @ERRMSG nvarchar(4000) = N'This subsidiary account is in conflict with relationship(s) '+@RULEID+' and therefore cannot be saved.'
      if @RULEID is not null
 raiserror(@ERRMSG,13,1)
        --raiserror('ERR_LEDGERACCOUNT_INVALIDRELATIONSHIP',13,1)

    end try
    begin catch
      exec dbo.USP_RAISE_ERROR
    end catch
  end
end