TR_PDACCOUNTSEGMENTVALUE_UPD_UPDATEACCOUNTSTRING

        CREATE trigger dbo.TR_PDACCOUNTSEGMENTVALUE_UPD_UPDATEACCOUNTSTRING
          on dbo.PDACCOUNTSEGMENTVALUE for update
        as
        declare @IsBasicGL Bit;
        declare @ShortID nvarchar(100);
        declare @Description nvarchar(60);
    declare @Length tinyint;
        select top 1 @IsBasicGL = T2.ISBASICGL, @ShortID = T1.SHORTDESCRIPTION, @Description = T1.DESCRIPTION, @Length = T2.LENGTH from inserted T1 join dbo.PDACCOUNTSTRUCTURE T2 on T1.PDACCOUNTSTRUCTUREID = T2.ID
        if @IsBasicGL = 0
            begin
                if UPDATE(SHORTDESCRIPTION)
                begin
                    declare
                      @CHANGEAGENTID uniqueidentifier,
                      @CURRENTDATE datetime = getdate();

                        if not ((len(@ShortID) = @Length) and (@Length > 0)) and not ((@Length = 0) and (len(@ShortID)>=(0)) and (len(@ShortID) < 101))
                            BEGIN
                                RAISERROR ('CK_DATAELEMENT_LENGTH', 16, 1)
                                ROLLBACK
                            END        

                        if not ((charindex('*',@ShortID) = 0) and (charindex('%',@ShortID) = 0) and (charindex('-',@ShortID) = 0) and (charindex(',',@ShortID) = 0) and (charindex('/',@ShortID) = 0) and (charindex(';',@ShortID) = 0) and (charindex('.',@ShortID) = 0))
                            BEGIN
                                RAISERROR ('CK_DATAELEMENT_SHORTIDVALIDCHARACTERS', 16, 1)
                                ROLLBACK
                            END                            

                        if @Description is null    
                            BEGIN
                                RAISERROR ('CK_DATAELEMENT_DESCRIPTION', 16, 1)
                                ROLLBACK
                            END                            

                        if not ((len(@Description)>(0)) and (len(@Description) < 61))    
                            BEGIN
                                RAISERROR ('CK_DATAELEMENT_DESCRIPTION', 16, 1)
                                ROLLBACK
                            END    


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

                    --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 GLACCOUNT) 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.GLACCOUNT on unpvt.ID = GLACCOUNT.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, 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 GLACCOUNT
                        inner join [ACCOUNTS] on GLACCOUNT.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.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(130)') as ACCTSTR
                      from
                        [ACCOUNTS] 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
                    ;
                end
        if update(DESCRIPTION)
        begin
            if not ((charindex('*',@Description) = 0) and (charindex('?',@Description) = 0))
            begin
              raiserror('CK_DATAELEMENT_DESCRIPTIONVALIDCHARACTERS', 16, 1)
              rollback
            end
        end
            end
        else
            begin
                if not ((len(@ShortID)>=(0)) and (len(@ShortID) < 101))    
                    BEGIN
                        RAISERROR ('CK_PDACCOUNTSEGMENTVALUE_SHORTDESCRIPTION', 16, 1)
                        ROLLBACK
                    END                        
            end