TR_ACCOUNTSTRUCTURE_INS

        CREATE trigger dbo.TR_ACCOUNTSTRUCTURE_INS
        on dbo.ACCOUNTSTRUCTURE
        after insert
        as
        set nocount on

        if (select count(*) from INSERTED) > 0
        begin

            declare @OVs table (ID uniqueidentifier, SEGMENTCOLUMN smallint, ELEMENTTYPECODE tinyint)
            declare @SegmentColumn tinyint
            declare @SQL nvarchar(2000)
            declare @Columns nvarchar(2000)

            update ACCOUNTSTRUCTURE
            set SEGMENTCOLUMN = (select V2.NUM
                from
                (select ID, row_number() over (order by SEQUENCE) as RowNumber
                from dbo.ACCOUNTSTRUCTURE
                where SEGMENTCOLUMN = 0) V1
                inner join
                (select top 30 NUM, row_number() over (order by NUM) as RowNumber
                from dbo.NUMBERS
                where not exists (select * from dbo.ACCOUNTSTRUCTURE where ACCOUNTSTRUCTURE.SEGMENTCOLUMN = NUMBERS.NUM)
                order by NUM) V2
                on V1.RowNumber = V2.RowNumber
                where V1.ID = ACCOUNTSTRUCTURE.ID)
             output inserted.ID, inserted.SEGMENTCOLUMN, inserted.ELEMENTTYPECODE into @OVs
            where SEGMENTCOLUMN = 0

            declare Cur Cursor fast_forward
            for
            select SEGMENTCOLUMN
            from @OVs
            where ELEMENTTYPECODE = 1

                open Cur
                fetch next from Cur into @SegmentColumn
                while @@Fetch_Status = 0
                    begin
                    set @SQL = 'if not exists (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[dbo].[LEDGERACCOUNT]'') AND name = N''IX_LEDGERACCOUNT_DATAELEMENT'+convert(nvarchar(2),@SegmentColumn)+'ID_DYN'')   
                        create index IX_LEDGERACCOUNT_DATAELEMENT'+convert(nvarchar(2),@SEGMENTCOLUMN)+'ID_DYN on dbo.LEDGERACCOUNT (DATAELEMENT'+convert(nvarchar(2),@SEGMENTCOLUMN)+'ID)'
                    exec (@SQL)                
                    fetch next from Cur into @SegmentColumn
                    end
                close cur
                deallocate cur

                select @Columns = (select top 15 'DATAELEMENT'+convert(nvarchar(2),SEGMENTCOLUMN)+'ID,'
                    from dbo.ACCOUNTSTRUCTURE
              where ELEMENTTYPECODE = 1
                    order by SEGMENTCOLUMN
                    for xml path(''))

                set @SQL = 'CREATE NONCLUSTERED INDEX [IX_LEDGERACCOUNT_DATAELEMENTS_DYN] ON [dbo].[LEDGERACCOUNT] 
              ('+substring(@Columns,1,len(@Columns)-1)+')'

                  if exists (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.LEDGERACCOUNT') AND name = N'IX_LEDGERACCOUNT_DATAELEMENTS_DYN')
                      set @SQL = @SQL+' with (DROP_EXISTING=ON)'

            exec (@SQL)

                if (select count(*) from dbo.ACCOUNTSTRUCTURE where ELEMENTTYPECODE = 1) > 15
                    begin
                    select @Columns = (select top 15 'DATAELEMENT'+convert(nvarchar(2),SEGMENTCOLUMN)+'ID,'
                      from accountstructure
                      where SEGMENTCOLUMN > (select max(SEGMENTCOLUMN) from (select top 15 SEGMENTCOLUMN from dbo.ACCOUNTSTRUCTURE where ELEMENTTYPECODE = 1) V1)
                      order by SEGMENTCOLUMN
                      for xml path(''))

                  set @SQL = 'CREATE NONCLUSTERED INDEX [IX_LEDGERACCOUNT_DATAELEMENTS_ADDITIONAL_DYN] ON [dbo].[LEDGERACCOUNT] 
                      ('+substring(@Columns,1,len(@Columns)-1)+')
                      INCLUDE (ACCOUNTSTRING,ID)'

                    if exists (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.LEDGERACCOUNT') AND name = N'IX_LEDGERACCOUNT_DATAELEMENTS_ADDITIONAL_DYN')
                        set @SQL = @SQL+' with (DROP_EXISTING=ON)'

                    exec (@SQL)

                    end

                end