TR_ACCOUNTSTRUCTURE_INSUPD

CREATE trigger dbo.TR_ACCOUNTSTRUCTURE_INSUPD on dbo.ACCOUNTSTRUCTURE
for insert, update not for replication as
if update(SEPARATORCODE) and exists(select 1 from dbo.LEDGERACCOUNT)
begin
declare @SEPARATOR nvarchar(1), @CHANGEAGENTID uniqueidentifier, @CURRENTDATE datetime;
select top 1 @SEPARATOR = case SEPARATORCODE 
  when 1 then '-'
  when 2 then ','
  when 3 then '/'
  when 4 then ';'
  when 5 then '.'
  else '-' end
from dbo.ACCOUNTSTRUCTURE;
--declare @msg nvarchar(100) = N'Separator: ' + @separator

--raiserror(@msg,13,1);

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