TR_DATAELEMENT_UPD_UPDATEACCOUNTSTRING

CREATE trigger dbo.TR_DATAELEMENT_UPD_UPDATEACCOUNTSTRING
  on dbo.DATAELEMENT for update
as
if UPDATE(SHORTID)
begin
declare
  @CHANGEAGENTID uniqueidentifier,
  @CURRENTDATE datetime = getdate();

exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
declare @SEPARATOR nvarchar(1);
select top 1 @SEPARATOR = SEPARATOR from dbo.ACCOUNTSTRUCTURE;

--First CTE limits updated accounts to only those which are using the updated dataelements

with [ACCOUNTS] as
(
  select unpvt.ID 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 LEDGERACCOUNT) 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.LEDGERACCOUNT on unpvt.ID = LEDGERACCOUNT.ID
  inner join inserted on unpvt.DATAELEMENTID = inserted.ID
)
--This CTE has all elements for the accounts in ACCOUNTS

, [ELEMENTS] as
(
  select unpvt.ID, DATAELEMENTID, SHORTID, SEGMENTSEQUENCE
  from
    (select LEDGERACCOUNT.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 LEDGERACCOUNT
    inner join [ACCOUNTS] on LEDGERACCOUNT.ID = ACCOUNTS.ID) 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
),
--This CTE actually creates the updated account strings for each account

CTE as
(
  select 
    ID, 
    ACCT.list.value('.','nvarchar(130)') as ACCTSTR
  from
    [ACCOUNTS] 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