At 03:35 PM 16/11/2003 +0100, you wrote:

>Hi,
>
>I never used triggers/stored procedures, but I think the moment to start
>with has come... See this table:
>
>CREATE TABLE WT_PERS (
> IPN INTEGER NOT NULL,
> PERS_BDG VARCHAR(20),
> PERS_NUMBER VARCHAR(20),
> PERS_NAME VARCHAR(50),
> etc ....
> PRIMARY KEY (IPN)
>);
>
>When a new person is inserted, the field PERS_NUMBER is not included in
>the insert statement.
>
>What I wanna do is, after an insert has been performed, the field
>PERS_NUMBER should automatically get the last (highest) used
>PERS_NUMBER incremented by 1

This is not a safe way to increment numbers in a multi-user
environment. If it is a plain number, create a generator for it:

create generator gen_pers_number;
commit;
set generator gen_pers_number to n; (your starting number)

>So I guess an after-insert-trigger

BEFORE INSERT for modifying data.

>that calls a stored procedure

a trigger is a stored procedure itself - a special kind that gets called
automatically when the DML event occurs. It can call other SPs but it
doesn't need to here.

>which
>updates the PERS_NUMBER would be the way to go.
>
>How would such a trigger/stored proc look like?

set term ^;
create trigger bi_WT_PERS for WT_PERS
active before insert position 0 as
begin
if (NEW.PERS_NUMBER is null) then
NEW.PERS_NUMBER = cast(gen_id(gen_pers_number, 1) as varchar(20));
end ^
set term ;^

heLen