TR_PDACCOUNTSTRUCTURE_INS

CREATE trigger [dbo].[TR_PDACCOUNTSTRUCTURE_INS] on dbo.PDACCOUNTSTRUCTURE
for insert
as
set nocount on
declare @SysID uniqueidentifier
declare @IsBasicGL bit
declare @ChangeAgentID uniqueidentifier 
declare @SegmentTableID uniqueidentifier;
declare @Description nvarchar(100);            
declare @ID uniqueidentifier = NEWID();

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

if @IsBasicGL = 1
begin
  if exists(select  COUNT(ID) from PDACCOUNTSTRUCTURE where DESCRIPTION is not null group by PDACCOUNTSYSTEMID,DESCRIPTION having COUNT(ID) > 1)
  BEGIN
    RAISERROR ('UIX_PDACCOUNTSTRUCTURE_DESCRIPTION_PDACCOUNTSYSTEMID', 16, 1)
    ROLLBACK
  END    

  declare @Length int = 0    
  declare @SegmentType int = 0    
  select top 1 @Length = LENGTH, @SegmentType = SEGMENTTYPE from inserted  order by DATECHANGED

  if @SegmentType = 1
  begin
    If not (@Length < 101)
      BEGIN
        RAISERROR ('CK_PDACCOUNTSTRUCTURE_VALIDMAXMINLENGTH', 16, 1)
        ROLLBACK
      END    
  end
  else
    If not (@Length > 0 and @Length < 101)
      BEGIN
        RAISERROR ('CK_PDACCOUNTSTRUCTURE_VALIDMAXMINLENGTH', 16, 1)
        ROLLBACK
      END                      

  if exists(select * from inserted where segmenttype != 1)
    exec USP_GLACCOUNT_REFRESH2 @SysID, @ChangeAgentID

  if exists(select * from inserted where segmenttype = 3)    
  begin
    exec USP_PDACCOUNTSEGMENTMAPPINGVIEW_RECREATE

    insert into dbo.PDACCOUNTSEGMENTMAPPING (ID,PDACCOUNTSTRUCTUREID, LONGDESCRIPTIONID, ISDEFAULT, ISCONSTANTVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select @ID,ID ,@ID, 1, ISCONSTANTVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED from inserted
  end 

  if exists(select * from inserted where segmenttype = 4)    
    exec USP_PDCOMPOSITESEGMENTMAPPINGVIEW_RECREATE
end 
else if (select count(*) from INSERTED) > 0 -- Do not run if no records were inserted

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

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

  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].[GLACCOUNT]'') AND name = N''IX_GLACCOUNT_DATAELEMENT'+convert(nvarchar(2),@SegmentColumn)+'ID_DYN'')   
      create index IX_GLACCOUNT_DATAELEMENT'+convert(nvarchar(2),@SEGMENTCOLUMN)+'ID_DYN on dbo.GLACCOUNT (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.PDACCOUNTSTRUCTURE
  where ELEMENTTYPECODE = 1 and PDACCOUNTSYSTEMID = @SysID
    order by SEGMENTCOLUMN
    for xml path(''))

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

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

  exec (@SQL)

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

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

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

    exec (@SQL)
  end

  if (select count(1) from dbo.CLOSINGREQUIREMENT where PDACCOUNTSYSTEMID = @SysID) > 0
  begin
    declare @CurrentDate datetime
    set @CurrentDate = getdate()

    insert into dbo.CLOSINGREQUIREMENTDETAIL(CLOSINGREQUIREMENTID, ACCOUNTSTRUCTUREID, ACCOUNTREQUIREMENTSCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
    select CLOSINGREQUIREMENTID, ACCOUNTSTRUCTUREID, case ELEMENTTYPECODE when 1 then 3 else 0 end, @ChangeAgentID, @ChangeAgentID, @CurrentDate, @CurrentDate from
    (select ID as CLOSINGREQUIREMENTID from dbo.CLOSINGREQUIREMENT where PDACCOUNTSYSTEMID = @SysID) t
    cross join
    (select ID as ACCOUNTSTRUCTUREID, ELEMENTTYPECODE from inserted) t2
  end

end