IF (NOT EXISTS (select Table_name from SysTable where LCase(Table_name)=LCase('SubsidyNormativ'))) THEN
   create table SubsidyNormativ 
   (
     Id integer not null default autoincrement,
	 PeriodStart date,
	 PeriodEnd date,
	 Normativ numeric(12,2),
	 primary key (Id)
   )
END IF
GO

if exists (select * from SYSTRIGGERS where LCase(trigname)=lcase('TrigSubsidyCheckPeriods'))
  then
  drop trigger TrigSubsidyCheckPeriods
end if

GO

create TRIGGER "TrigSubsidyCheckPeriods" after insert,update order 1
on "DBA".SubsidyNormativ
referencing new as SKNEW
for each row
begin 
  declare error exception for sqlstate value '40W02';

  if (exists(select Id from SubsidyNormativ where Id <> SKNEW.Id and PeriodStart <= SKNEW.Periodend and PeriodEnd >= SKNEW.PeriodStart)) then 
    signal error;
  end if;
end
go

if (not exists(select Id from SubsidyNormativ)) then
  insert into SubsidyNormativ (PeriodStart, PeriodEnd, Normativ)
    values ('01.01.2008','31.12.2008',1.10);
  insert into SubsidyNormativ (PeriodStart, PeriodEnd, Normativ)
    values ('01.01.2009','31.12.2009',1.15);
end if
go

IF (EXISTS(Select * from sys.sysprocedure where LCase(proc_name) = LCase('FillRLegloDni')))
THEN Drop procedure FillRLegloDni
END IF

GO

create procedure
DBA.FillRLegloDni()
begin
  declare @sqlTemp text;
  declare @tmpSum integer;
  declare @tmpD1 date;
  declare @tmpD2 date;
  declare @Normativ numeric(12,2);
  execute immediate 'delete from #RLegloDni';
  set @tmpD1=bvrData1;
  set @tmpD2=bvrData2;
  if bvrData1 <= bvrData2 then
    while(bvrData1 <= bvrData2) loop
      set @tmpSum=0;
      set @sqlTemp='insert into #RLegloDni Values(''' || DateFormat(bvrData1,'yyyy.mm.dd') || ''',';
	  select isNull(Normativ,0) into @Normativ from SubsidyNormativ where PeriodStart <= bvrData1 and PeriodEnd >= bvrData1;
      for f3 as curs3 scroll cursor for select PriceKind from PriceKinds where PriceKind in (2,4,5,6,7,8) order by PriceKind asc do
        set @tmpSum=@tmpSum+GetNumBookForPKDay(bvrData1,PriceKind);
        set @sqlTemp=@sqlTemp || GetNumBookForPKDay(bvrData1,PriceKind) || ',' end for;
      set @sqlTemp=@sqlTemp || @tmpSum || ','||@Normativ||',' || @Normativ*@tmpSum || ')';
      execute immediate @sqlTemp;
      set bvrData1=DateAdd(Day,1,bvrData1)
    end loop
  end if;
  set bvrData1=@tmpD1;
  set bvrData2=@tmpD2
end

go