Приводится исходный текст самого первого варианта триггера, непосредственно полученного из программы-генератора раскрывшей макросы. Сразу же потребовалось сделать ручные правки. В частности 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