> 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.

as a rule - I'd never call an UPDATE statement on the same table from an
after (or before) update trigger on the said table.
I'm sure I do what you think you want to do without resorting to this tactic
but I can't think clearly about what you are trying to do... what is the
logic of your attempt here?
Alan

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