TR_PDACCOUNTSTRUCTURE_INSUPD

CREATE trigger dbo.TR_PDACCOUNTSTRUCTURE_INSUPD on dbo.PDACCOUNTSTRUCTURE
for insert, update not for replication
as
declare @SysID uniqueidentifier
declare @IsBasicGL bit;

select top 1  @SysID=PDACCOUNTSYSTEMID, @IsBasicGL = ISBASICGL from inserted where ADDEDBYID is not null order by DATEADDED

if @IsBasicGL = 0    and update(SEPARATORCODE) and exists(select 1 from dbo.GLACCOUNT)
  and exists(select 1 from inserted I inner join deleted D on I.ID = D.ID and I.SEPARATORCODE <> D.SEPARATORCODE)
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.PDACCOUNTSTRUCTURE where PDACCOUNTSYSTEMID=@SysID;

  exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
  set @CURRENTDATE = getdate();

  ;with [ELEMENTS] as
  (
    select unpvt.ID, DATAELEMENTID, SHORTDESCRIPTION, SEGMENTSEQUENCE
    from
    (select GLACCOUNT.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.GLACCOUNT where GLACCOUNT.PDACCOUNTSYSTEMID = @SysID) 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.PDACCOUNTSEGMENTVALUE on unpvt.DATAELEMENTID = PDACCOUNTSEGMENTVALUE.ID
    inner join dbo.PDACCOUNTSTRUCTURE on PDACCOUNTSEGMENTVALUE.PDACCOUNTSTRUCTUREID = PDACCOUNTSTRUCTURE.ID
  ),
  --This CTE actually creates the updated account strings for each account

  CTE as
  (
    select 
    ID, 
    ACCT.list.value('.','nvarchar(100)') as ACCTSTR
    from
    dbo.GLACCOUNT T1 
    cross apply
    (
      select isnull(SHORTDESCRIPTION+@SEPARATOR,'')
      from [ELEMENTS]
      where T1.ID = [ELEMENTS].ID
      order by SEGMENTSEQUENCE
      for xml path(''), type
    ) ACCT (list)
  )
  update GLACCOUNT set
    ACCOUNTSTRING = left(T1.acctstr, len(T1.acctstr)-1),
    CHANGEDBYID = @CHANGEAGENTID,
    DATECHANGED = @CURRENTDATE
  from
    CTE T1
  where T1.ID = GLACCOUNT.ID and GLACCOUNT.PDACCOUNTSYSTEMID = @SysID
  ;
end