TR_LEDGERACCOUNT_INSUPD

CREATE trigger dbo.TR_LEDGERACCOUNT_INSUPD
  on dbo.LEDGERACCOUNT 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 LA
    inner join dbo.ACCOUNTINGELEMENTRELATIONSHIPDETAIL AERD on
      (AERD.DATAELEMENT1ID is null or AERD.DATAELEMENT1ID = LA.DATAELEMENT1ID)
      and
      (AERD.DATAELEMENT2ID is null or AERD.DATAELEMENT2ID = LA.DATAELEMENT2ID)
      and
      (AERD.DATAELEMENT3ID is null or AERD.DATAELEMENT3ID = LA.DATAELEMENT3ID)
      and
      (AERD.DATAELEMENT4ID is null or AERD.DATAELEMENT4ID = LA.DATAELEMENT4ID)
      and
      (AERD.DATAELEMENT5ID is null or AERD.DATAELEMENT5ID = LA.DATAELEMENT5ID)
      and
      (AERD.DATAELEMENT6ID is null or AERD.DATAELEMENT6ID = LA.DATAELEMENT6ID)
      and
      (AERD.DATAELEMENT7ID is null or AERD.DATAELEMENT7ID = LA.DATAELEMENT7ID)
      and
      (AERD.DATAELEMENT8ID is null or AERD.DATAELEMENT8ID = LA.DATAELEMENT8ID)
      and
      (AERD.DATAELEMENT9ID is null or AERD.DATAELEMENT9ID = LA.DATAELEMENT9ID)
      and
      (AERD.DATAELEMENT10ID is null or AERD.DATAELEMENT10ID = LA.DATAELEMENT10ID)
      and
      (AERD.DATAELEMENT11ID is null or AERD.DATAELEMENT11ID = LA.DATAELEMENT11ID)
      and
      (AERD.DATAELEMENT12ID is null or AERD.DATAELEMENT12ID = LA.DATAELEMENT12ID)
      and
      (AERD.DATAELEMENT13ID is null or AERD.DATAELEMENT13ID = LA.DATAELEMENT13ID)
      and
      (AERD.DATAELEMENT14ID is null or AERD.DATAELEMENT14ID = LA.DATAELEMENT14ID)
      and
      (AERD.DATAELEMENT15ID is null or AERD.DATAELEMENT15ID = LA.DATAELEMENT15ID)
      and
      (AERD.DATAELEMENT16ID is null or AERD.DATAELEMENT16ID = LA.DATAELEMENT16ID)
      and
      (AERD.DATAELEMENT17ID is null or AERD.DATAELEMENT17ID = LA.DATAELEMENT17ID)
      and
      (AERD.DATAELEMENT18ID is null or AERD.DATAELEMENT18ID = LA.DATAELEMENT18ID)
      and
      (AERD.DATAELEMENT19ID is null or AERD.DATAELEMENT19ID = LA.DATAELEMENT19ID)
      and
      (AERD.DATAELEMENT20ID is null or AERD.DATAELEMENT20ID = LA.DATAELEMENT20ID)
      and
      (AERD.DATAELEMENT21ID is null or AERD.DATAELEMENT21ID = LA.DATAELEMENT21ID)
      and
      (AERD.DATAELEMENT22ID is null or AERD.DATAELEMENT22ID = LA.DATAELEMENT22ID)
      and
      (AERD.DATAELEMENT23ID is null or AERD.DATAELEMENT23ID = LA.DATAELEMENT23ID)
      and
      (AERD.DATAELEMENT24ID is null or AERD.DATAELEMENT24ID = LA.DATAELEMENT24ID)
      and
      (AERD.DATAELEMENT25ID is null or AERD.DATAELEMENT25ID = LA.DATAELEMENT25ID)
      and
      (AERD.DATAELEMENT26ID is null or AERD.DATAELEMENT26ID = LA.DATAELEMENT26ID)
      and
      (AERD.DATAELEMENT27ID is null or AERD.DATAELEMENT27ID = LA.DATAELEMENT27ID)
      and
      (AERD.DATAELEMENT28ID is null or AERD.DATAELEMENT28ID = LA.DATAELEMENT28ID)
      and
      (AERD.DATAELEMENT29ID is null or AERD.DATAELEMENT29ID = LA.DATAELEMENT29ID)
      and
      (AERD.DATAELEMENT30ID is null or AERD.DATAELEMENT30ID = LA.DATAELEMENT30ID)
    inner join dbo.ACCOUNTINGELEMENTRELATIONSHIP AER on AERD.ACCOUNTINGELEMENTRELATIONSHIPID = AER.ID
    group by AER.ID, RELATIONSHIPID;
    declare @ERRMSG nvarchar(4000) = N'This 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

declare
  @CHANGEAGENTID uniqueidentifier,
  @CURRENTDATE datetime = getdate(),
  @SEPARATOR nvarchar(1);

exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
select top 1 @SEPARATOR = SEPARATOR from dbo.ACCOUNTSTRUCTURE;

;with [ELEMENTS] as
(
  select unpvt.ID, DATAELEMENTID, SHORTID, SEGMENTSEQUENCE
  from
    (select ID, DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID, 
    DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID, 
    DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID
    from inserted) as P
   unpivot
    (DATAELEMENTID for dColumn in 
      (DATAELEMENT1ID, DATAELEMENT2ID, DATAELEMENT3ID, DATAELEMENT4ID, DATAELEMENT5ID, DATAELEMENT6ID, DATAELEMENT7ID, DATAELEMENT8ID, DATAELEMENT9ID, DATAELEMENT10ID, 
      DATAELEMENT11ID, DATAELEMENT12ID, DATAELEMENT13ID, DATAELEMENT14ID, DATAELEMENT15ID, DATAELEMENT16ID, DATAELEMENT17ID, DATAELEMENT18ID, DATAELEMENT19ID, DATAELEMENT20ID, 
      DATAELEMENT21ID, DATAELEMENT22ID, DATAELEMENT23ID, DATAELEMENT24ID, DATAELEMENT25ID, DATAELEMENT26ID, DATAELEMENT27ID, DATAELEMENT28ID, DATAELEMENT29ID, DATAELEMENT30ID)
    ) as unpvt
  inner join dbo.DATAELEMENT on unpvt.DATAELEMENTID = DATAELEMENT.ID
  inner join dbo.ACCOUNTSTRUCTURE on DATAELEMENT.GLACCOUNTSTRUCTUREID = ACCOUNTSTRUCTURE.ID
),
CTE as
(
  select 
    ID, 
    ACCT.list.value('.','nvarchar(130)') as ACCTSTR
  from
    inserted t1
  cross apply
    (
      select isnull(SHORTID+@SEPARATOR,'')
      from [ELEMENTS]
      where t1.ID = [ELEMENTS].ID
      order by SEGMENTSEQUENCE
      for xml path(''), type
    ) ACCT (list)
)
update LEDGERACCOUNT set
  ACCOUNTSTRING = left(T1.acctstr, len(T1.acctstr)-1),
  CHANGEDBYID = @CHANGEAGENTID,
  DATECHANGED = @CURRENTDATE
from
  CTE T1
where T1.ID = LEDGERACCOUNT.ID
;

end