[Firehol-support] my trigger crashes the server

[Wolfgang Rohdewald]

I am using Firebird 2.0.1 SS on Windows 2000 Professionel.

I have a trigger which crashes this server.
After trying to simplify it, the server did  not crash
but seemed to hang - I could not even kill the process.

I simplified the problem further to what you can see below.
Now there is no crash but the trigger does not behave as
it should.

the second select should return those values for balance:
100,50,80,100,110,100 but it returns 
100,100,100,100,110,100 - in other words the trigger does
not recurse.

if I modify the update commands using sale=499 instead of 500
everything is correct.

on a related note, is there an easier way to select the
previous row using a multi field order? That idxfield
seems like a hack. (in my full application the order
consists of 4 fields)

If you need more information - I will be offline for some time
starting monday.

this is the full script showing the problem:

rollback;
connect 'cygwin:bug1';
drop database;
commit;
create database 'cygwin:bug1';
input 'ib_udf2.sql';
commit;

create table sales (
        sale bigint not null,
        amount numeric(18,2),
        custno integer,
        balance numeric(18,2));

alter table sales add constraint pk_sales primary key (sale);

alter table sales add
        idxfield computed by (lpad(custno,6,' ')||lpad(sale,10,' '));

set term ^;
create trigger au_sales for sales active after update as
declare variable prevsale bigint;
begin
        if (updating and coalesce(old.balance,-999995435.54)<>new.balance) then begin
                select first 1 sale from sales b where b.custno=new.custno and b.idxfield<new.idxfield
                order by custno desc, sale desc into :prevsale;
                update sales set balance=new.balance-new.amount
                where custno=new.custno and sale=:prevsale;
        end
end ^
set term ;^
commit;

insert into sales values(498,50,4073,100);
insert into sales values(420,40,4074,100);
insert into sales values(450,30,4074,100);
insert into sales values(499,20,4074,100);
insert into sales values(500,10,4074,100);
insert into sales values(510,10,4074,100);
commit;
select custno,sale,amount,balance from sales;
update sales set balance=balance+10 where custno=4074 and sale=500;
select custno,sale,amount,balance from sales;
update sales set balance=balance-10 where custno=4074 and sale=500;
select custno,sale,amount,balance from sales;
update sales set balance=balance+10 where custno=4074 and sale=500;
select custno,sale,amount,balance from sales;
update sales set balance=balance-10 where custno=4074 and sale=500;
select custno,sale,amount,balance from sales;


-- 
Wolfgang