Make your own free website on Tripod.com

Объектно-реляционная БД Ultima-S

Исходный текст триггера поддержания сальдовой таблицы

Приводится исходный текст самого первого варианта триггера, непосредственно полученного из программы-генератора раскрывшей макросы. Сразу же потребовалось сделать ручные правки. В частности MSSQL не смог скомпилировать слишком длинный оператор insert из-за слишком большого числа вовлеченных таблиц. Поэтому я заменил его на аналог через курсор. Далее А. Тихановский произведет серьезную оптимизацию триггера удалив курсор и создав микрокеши значительно поднявшие его производительность. Я с Л.Фрейдиным добавлю далее поддержку механизма в 20 уровней аналитики и категориальной аналитики. Последнюю оптимизацию для больших объемов я выполнил в летом 1999г., фактически перепроектировав алгоритм и таблицу Saldo. Тем не менее основные изложенные идеи и концепции остались и доказали свою жизненность, но к ним добавились новые.

 

if exists (select * from sysobjects where id = object_id('dbo.SaldoInformator') and sysstat & 0xf = 8)

drop trigger dbo.SaldoInformator

GO

CREATE TRIGGER SaldoInformator

ON Complect

FOR INSERT, UPDATE, DELETE

as

declare @UDN int, @u1 int, @u2 int, @u3 int, @u4 int, @u5 int,

@u6 int, @u7 int, @u8 int,

@maxu1 int, @ict int, @idt int, @sct money, @sdt money

if exists (select * from inserted T, Acc where T.UDN=Acc.UDN or T.UDN=-Acc.UDN )

or exists (select * from deleted T, Acc where T.UDN=Acc.UDN or T.UDN=-Acc.UDN )

begin

declare C cursor

for

select distinct abs(T.UDN), T.u1, T.u2, T.u3, T.u4, T.u5 , T.u6 , T.u7 , T.u8 from inserted T

where not exists (

select T.UDN, T.u1, T.u2, T.u3, T.u4, T.u5 , T.u6 , T.u7 , T.u8 from Saldo where abs(T.UDN)= Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1= Saldo.u1 )

open C

fetch next from C into @UDN, @u1, @u2, @u3, @u4, @u5, @u6, @u7, @u8

while @@FETCH_STATUS <> -1

begin

if @@FETCH_STATUS <> -2

begin

select @maxu1=max( Saldo.u1) from Saldo

where UDN=@UDN and u2=@u2 and u3=@u3 and u4=@u4 and u5=@u5 and u6=@u6 and u7=@u7 and u8=@u8 and u1<@u1

if @maxu1 is not null begin

select @ict=isnull(ict, 0), @idt=isnull(idt, 0), @sdt=isnull(sdt, 0), @sct=isnull(sct, 0) from Saldo

where UDN=@UDN and u2=@u2 and u3=@u3 and u4=@u4 and u5=@u5 and u6=@u6 and u7=@u7 and u8=@u8 and u1=@maxu1

end

else begin

select @ict= 0, @idt= 0, @sdt= 0, @sct= 0

end

insert Saldo(UDN, u1, u2, u3, u4, u5, u6, u7, u8, ic, id, sd, sc, ict, idt, sdt, sct)

select @UDN, @u1, @u2, @u3, @u4, @u5, @u6, @u7, @u8 , 0, 0, 0, 0, @ict, @idt, @sdt, @sct

end

fetch next from C into @UDN, @u1, @u2, @u3, @u4, @u5, @u6, @u7, @u8

end

deallocate C

-- C fields

update Saldo

set

Saldo.sc= Saldo.sc-(select sum(T.s) from deleted T where T.UDN= Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1= Saldo.u1),

Saldo.ic= Saldo.ic-(select sum(T.i) from deleted T where T.UDN= Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1= Saldo.u1)

where exists( select distinct Saldo.UDN, Saldo.u1, Saldo.u2, Saldo.u3, Saldo.u4, Saldo.u5, Saldo.u6, Saldo.u7, Saldo.u8 from deleted T where T.UDN= Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1= Saldo.u1)

update Saldo

set

Saldo.sc= Saldo.sc+(select sum(T.s) from inserted T where T.UDN= Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1= Saldo.u1),

Saldo.ic= Saldo.ic+(select sum(T.i) from inserted T where T.UDN= Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1= Saldo.u1)

where exists( select distinct Saldo.UDN, Saldo.u1, Saldo.u2, Saldo.u3, Saldo.u4, Saldo.u5, Saldo.u6, Saldo.u7, Saldo.u8 from inserted T where T.UDN= Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1= Saldo.u1)

 

--D fields

update Saldo

set

Saldo.sd= Saldo.sd-(select sum(T.s) from deleted T where T.UDN= - Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1= Saldo.u1),

Saldo.id= Saldo.id-(select sum(T.i) from deleted T where T.UDN= - Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1= Saldo.u1)

where exists( select distinct Saldo.UDN, Saldo.u1, Saldo.u2, Saldo.u3, Saldo.u4, Saldo.u5, Saldo.u6, Saldo.u7, Saldo.u8 from deleted T where T.UDN= - Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1= Saldo.u1)

update Saldo

set

Saldo.sd= Saldo.sd+(select sum(T.s) from inserted T where T.UDN= - Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1= Saldo.u1),

Saldo.id= Saldo.id+(select sum(T.i) from inserted T where T.UDN= - Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1= Saldo.u1)

where exists( select distinct Saldo.UDN, Saldo.u1, Saldo.u2, Saldo.u3, Saldo.u4, Saldo.u5, Saldo.u6, Saldo.u7, Saldo.u8 from inserted T where T.UDN= - Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1= Saldo.u1)

--CT fields

update Saldo

set

Saldo.sct= Saldo.sct-(select sum(T.s) from deleted T where T.UDN= Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1<= Saldo.u1),

Saldo.ict= Saldo.ict-(select sum(T.i) from deleted T where T.UDN= Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1<= Saldo.u1)

where exists( select distinct Saldo.UDN, Saldo.u1, Saldo.u2, Saldo.u3, Saldo.u4, Saldo.u5, Saldo.u6, Saldo.u7, Saldo.u8 from deleted T where T.UDN= Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1<= Saldo.u1)

update Saldo

set

Saldo.sct= Saldo.sct+(select sum(T.s) from inserted T where T.UDN= Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1<= Saldo.u1),

Saldo.ict= Saldo.ict+(select sum(T.i) from inserted T where T.UDN= Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1<= Saldo.u1)

where exists( select distinct Saldo.UDN, Saldo.u1, Saldo.u2, Saldo.u3, Saldo.u4, Saldo.u5, Saldo.u6, Saldo.u7, Saldo.u8 from inserted T where T.UDN= Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1<= Saldo.u1)

--DT fields

update Saldo

set

Saldo.sdt= Saldo.sdt-(select sum(T.s) from deleted T where T.UDN= - Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1<= Saldo.u1),

Saldo.idt= Saldo.idt-(select sum(T.i) from deleted T where T.UDN= - Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1<= Saldo.u1)

where exists( select distinct Saldo.UDN, Saldo.u1, Saldo.u2, Saldo.u3, Saldo.u4, Saldo.u5, Saldo.u6, Saldo.u7, Saldo.u8 from deleted T where T.UDN= - Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1<= Saldo.u1)

update Saldo

set

Saldo.sdt= Saldo.sdt+(select sum(T.s) from inserted T where T.UDN= - Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1<= Saldo.u1),

Saldo.idt= Saldo.idt+(select sum(T.i) from inserted T where T.UDN= - Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1<= Saldo.u1)

where exists( select distinct Saldo.UDN, Saldo.u1, Saldo.u2, Saldo.u3, Saldo.u4, Saldo.u5, Saldo.u6, Saldo.u7, Saldo.u8 from inserted T where T.UDN= - Saldo.UDN and T.u2= Saldo.u2 and T.u3= Saldo.u3 and T.u4= Saldo.u4 and T.u5= Saldo.u5 and T.u6= Saldo.u6 and T.u7= Saldo.u7 and T.u8= Saldo.u8 and T.u1<= Saldo.u1)

end

GO