TR_LEDGERPREFERENCE_INSUPD

create trigger dbo.TR_LEDGERPREFERENCE_INSUPD
on dbo.LEDGERPREFERENCE for insert, update
as
if update(SEPARATOR) and exists(select 1 from dbo.LEDGERACCOUNT)
begin
declare @SEPARATOR nvarchar(1), @CHANGEAGENTID uniqueidentifier, @CURRENTDATE datetime;
select top 1 @SEPARATOR = SEPARATOR from inserted;
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
set @CURRENTDATE = getdate();

;with [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 dbo.LEDGERACCOUNT) 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
    dbo.LEDGERACCOUNT 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