Subject Re[2]: [IBO] Re: Primary Key of Query set by Trigger
Author Dieter Tremel
Hello Helen,

HBT> I think you are needlessly concerned about the atomicity of
HBT> generators. *Every* pull from a generator is atomic, by
HBT> nature. Generators are completely outside transaction control (the *only*
HBT> thing in FB/IB that is so). Once generated, a number cannot be generated
HBT> again for anything else.

Yes, I take so much care in this special case, because the SP doesn't
use a generator. The idea of the SP is to have a table with returned
numbers because they have been unused. If there is a 'Reusable Number'
it is returned, else an incremented is used. Look (sorry for the mix
of German and English in the code, but as I recognized not far ago you
can understand at least a litte bit German, can't you?):

ALTER PROCEDURE GETNEXT_NKREISNUMMER (
NKREIS /* RDB$71 */ VARCHAR(12) CHARACTER SET WIN1252 )
RETURNS (
NUMMER /* RDB$72 */ INTEGER )
AS
declare variable newdate Date;
begin
select letztenr, neu_ab from Nummernkreise
where Nummernkreis=:nkreis and POOL='F'
into :Nummer, :newdate;
if (:Nummer is null) then
exception NK_UNKNOWN_NKREIS;
if (:newdate <= current_date) then
exception NK_NEU_AB;

select Max(letztenr) from Nummernkreise
where Nummernkreis=:nkreis and POOL='T'
into :Nummer;
if (:Nummer is null) then begin
select letztenr+1 from Nummernkreise
where nummernkreis=:nkreis and POOL='F'
into :Nummer;
update Nummernkreise set letztenr=:Nummer
where Nummernkreis=:nkreis and POOL='F';
end else begin
delete from Nummernkreise where Nummernkreis=:nkreis and letztenr=:Nummer and POOL='T';
end
END^

CREATE TABLE NUMMERNKREISE (
NUMMERNKREIS BELEGART_D /* Varchar(12) */ NOT NULL
, LETZTENR BELEGNR_D /* Integer */ NOT NULL
, POOL BOOL_D /* Varchar(1) */ NOT NULL /* Defaulted */
, NEU_AB DATE
, BEMERKUNG MEMO_D
, CONSTRAINT PK_NUMMERNKREISE
PRIMARY KEY ( NUMMERNKREIS, LETZTENR, POOL )
)

So I must precise my question: Can I have a real atomic operation in
FB without generators by using the transactions the right way?

Since the system will be used only by few users concurrently the problem will not
be of high priority, but I want to do it well.

Thank You
Dieter Tremel