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
|
Trigger Definition
