TR_BALANCINGENTRYELEMENT_INSUPD

CREATE trigger dbo.TR_BALANCINGENTRYELEMENT_INSUPD
  on dbo.BALANCINGENTRYELEMENT for insert, update
as
if
  (
    (substring(columns_updated(),1,1) & 126 > 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)
  )
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 BEE
      inner join dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL AERD on
        (AERD.DATAELEMENT1ID is null or AERD.DATAELEMENT1ID = BEE.DATAELEMENT1ID)
        and
        (AERD.DATAELEMENT2ID is null or AERD.DATAELEMENT2ID = BEE.DATAELEMENT2ID)
        and
        (AERD.DATAELEMENT3ID is null or AERD.DATAELEMENT3ID = BEE.DATAELEMENT3ID)
        and
        (AERD.DATAELEMENT4ID is null or AERD.DATAELEMENT4ID = BEE.DATAELEMENT4ID)
        and
        (AERD.DATAELEMENT5ID is null or AERD.DATAELEMENT5ID = BEE.DATAELEMENT5ID)
        and
        (AERD.DATAELEMENT6ID is null or AERD.DATAELEMENT6ID = BEE.DATAELEMENT6ID)
        and
        (AERD.DATAELEMENT7ID is null or AERD.DATAELEMENT7ID = BEE.DATAELEMENT7ID)
        and
        (AERD.DATAELEMENT8ID is null or AERD.DATAELEMENT8ID = BEE.DATAELEMENT8ID)
        and
        (AERD.DATAELEMENT9ID is null or AERD.DATAELEMENT9ID = BEE.DATAELEMENT9ID)
        and
        (AERD.DATAELEMENT10ID is null or AERD.DATAELEMENT10ID = BEE.DATAELEMENT10ID)
        and
        (AERD.DATAELEMENT11ID is null or AERD.DATAELEMENT11ID = BEE.DATAELEMENT11ID)
        and
        (AERD.DATAELEMENT12ID is null or AERD.DATAELEMENT12ID = BEE.DATAELEMENT12ID)
        and
        (AERD.DATAELEMENT13ID is null or AERD.DATAELEMENT13ID = BEE.DATAELEMENT13ID)
        and
        (AERD.DATAELEMENT14ID is null or AERD.DATAELEMENT14ID = BEE.DATAELEMENT14ID)
        and
        (AERD.DATAELEMENT15ID is null or AERD.DATAELEMENT15ID = BEE.DATAELEMENT15ID)
        and
        (AERD.DATAELEMENT16ID is null or AERD.DATAELEMENT16ID = BEE.DATAELEMENT16ID)
        and
        (AERD.DATAELEMENT17ID is null or AERD.DATAELEMENT17ID = BEE.DATAELEMENT17ID)
        and
        (AERD.DATAELEMENT18ID is null or AERD.DATAELEMENT18ID = BEE.DATAELEMENT18ID)
        and
        (AERD.DATAELEMENT19ID is null or AERD.DATAELEMENT19ID = BEE.DATAELEMENT19ID)
        and
        (AERD.DATAELEMENT20ID is null or AERD.DATAELEMENT20ID = BEE.DATAELEMENT20ID)
        and
        (AERD.DATAELEMENT21ID is null or AERD.DATAELEMENT21ID = BEE.DATAELEMENT21ID)
        and
        (AERD.DATAELEMENT22ID is null or AERD.DATAELEMENT22ID = BEE.DATAELEMENT22ID)
        and
        (AERD.DATAELEMENT23ID is null or AERD.DATAELEMENT23ID = BEE.DATAELEMENT23ID)
        and
        (AERD.DATAELEMENT24ID is null or AERD.DATAELEMENT24ID = BEE.DATAELEMENT24ID)
        and
        (AERD.DATAELEMENT25ID is null or AERD.DATAELEMENT25ID = BEE.DATAELEMENT25ID)
        and
        (AERD.DATAELEMENT26ID is null or AERD.DATAELEMENT26ID = BEE.DATAELEMENT26ID)
        and
        (AERD.DATAELEMENT27ID is null or AERD.DATAELEMENT27ID = BEE.DATAELEMENT27ID)
        and
        (AERD.DATAELEMENT28ID is null or AERD.DATAELEMENT28ID = BEE.DATAELEMENT28ID)
        and
        (AERD.DATAELEMENT29ID is null or AERD.DATAELEMENT29ID = BEE.DATAELEMENT29ID)
        and
        (AERD.DATAELEMENT30ID is null or AERD.DATAELEMENT30ID = BEE.DATAELEMENT30ID)
      inner join dbo.ACCOUNTINGELEMENTRELATIONSHIP AER on AERD.ACCOUNTINGELEMENTRELATIONSHIPID = AER.ID
      group by AER.ID, AER.RELATIONSHIPID
      declare @ERRMSG nvarchar(4000) = N'This balancing entry is in conflict with relationship(s) '+@RULEID+' and therefore cannot be saved.'
      if @RULEID is not null
        raiserror(@ERRMSG,13,1)
    end try
    begin catch
      exec dbo.USP_RAISE_ERROR
    end catch
  end
end