On Sunday 16 November 2003 22:37, Helen Borrie wrote:

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

well I know, but inserts are not frequent on this table, and only
performed by one single user, so I guess it should be safe here...

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

the staring number, you mean with an empty table?
In fact I have about 600 records in that table already, and the next
value would be like (SELECT MAX(PERS_NUMBER) FROM WT_PERS)+1

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

I'll be able to try this out tomorrow, thank you for your help!
Yves

> heLen
>
>
>
> ------------------------ Yahoo! Groups Sponsor
> ---------------------~--> Buy Ink Cartridges or Refill Kits for your
> HP, Epson, Canon or Lexmark Printer at MyInks.com. Free s/h on orders
> $50 or more to the US & Canada.
> http://www.c1tracking.com/l.asp?cid=5511
> http://us.click.yahoo.com/mOAaAA/3exGAA/qnsNAA/67folB/TM
> ---------------------------------------------------------------------
>~->
>
> To unsubscribe from this group, send an email to:
> firebird-support-unsubscribe@yahoogroups.com
>
>
>
> Your use of Yahoo! Groups is subject to
> http://docs.yahoo.com/info/terms/

--
Linux 2.4.22-1-k7 #1 Sat Sep 6 02:13:04 EST 2003 i686
23:09:01 up 24 min, 1 user, load average: 0.18, 0.46, 0.32