Hello.

I'm using Firebird 2.1 Classic Server.

Now, I have a table:

________________________________________________
/* Tabelle: TANGEBOTPOS */

CREATE TABLE TANGEBOTPOS (
TEILENR VARCHAR (8) CHARACTER SET ISO8859_1 NOT NULL COLLATE DE_DE,
MENGE DOUBLE PRECISION,
JAHR INTEGER NOT NULL,
NR INTEGER NOT NULL,
VKPREIS DOUBLE PRECISION,
LIEFERDATUM TIMESTAMP,
ID INTEGER NOT NULL,
INFO INTEGER,
POS INTEGER,
PREISBER INTEGER);



/* Primary keys definition */

ALTER TABLE TANGEBOTPOS ADD CONSTRAINT PK_TANGEBOTPOS PRIMARY KEY (ID);


/* Foreign keys definition */

ALTER TABLE TANGEBOTPOS ADD CONSTRAINT FK_TANGEBOTPOS FOREIGN KEY (JAHR, NR)
REFERENCES TANGEBOTE (JAHR, NR) ON UPDATE CASCADE;
ALTER TABLE TANGEBOTPOS ADD CONSTRAINT FK_TANGEBOTPOS1 FOREIGN KEY (TEILENR)
REFERENCES TTEILE (TEILENR) ON DELETE CASCADE ON UPDATE CASCADE;


/* Indices definition */

CREATE INDEX FK_TANGEBOTPOS ON TANGEBOTPOS (JAHR, NR);
CREATE INDEX FK_TANGEBOTPOS1 ON TANGEBOTPOS (TEILENR);
CREATE UNIQUE INDEX PK_TANGEBOTPOS ON TANGEBOTPOS (ID);

________________________________________________

The Table is referenced by another table tangebot. In the table tangebotpos
there are prositions of an offer.

Now, I have a Trigger:
________________________________________________________
AS
declare variable stp_anz double precision;
declare variable stp_preis double precision;
declare variable ang_preis double precision;
declare variable kunde integer;
BEGIN
ang_preis = 0;
NEW.ID = GEN_ID(TANGEBOTPOS_ID_GEN, 1);
if(new.vkpreis is null) then /* If the new price is not set */
begin
for select vanz, preis from tstaffelpreise where teilenr = new.teilenr /*
price model 1 */
order by vanz asc into :stp_anz, :stp_preis do
begin
if(new.menge >= :stp_anz) then ang_preis = stp_preis;
else break;
end
if(ang_preis = 0) then begin /* no price in price model set, next price
model */
select kundennr from tangebote where jahr = new.jahr and nr =
new.nr into :kunde;
select preis from tartkunde
where teilenr = new.teilenr and kundennr = :kunde into :ang_preis;
end
if((ang_preis is null)or(ang_preis = 0)) then /* no price in model 2 set,
last price model, normal price */
begin
select vkpreis from tteile where teilenr = new.teilenr into :ang_preis;
end
new.vkpreis = :ang_preis;
end
END
____________________________

The trigger works fine. It sets the right price, it searches in three steps
(Price model one, two or three, price model one are graduated prices and has
the first priority.

The same trigger I have created as update-trigger to change the price if the
part-no. (teilenr) or amaount is changed.

Now, I need to set the field "preisber" with the value 1 (if price model one
set the price), 2 (if price model two) or 3 if price model three)

But it does not work. No matter if I set another value in the trigger, the
value doensn't change.

For example:
_____________________
AS
declare variable stp_anz double precision;
declare variable stp_preis double precision;
declare variable ang_preis double precision;
declare variable kunde integer;
BEGIN
ang_preis = 0;
NEW.ID = GEN_ID(TANGEBOTPOS_ID_GEN, 1);
New.preisber = 123; /* This doesn't work */
...
_______________
The ID will set, the price too (vkpreis), but no other field were set!

New.menge = 222; does not work too.

What can it be? What is wrong?

Thank you for your help.


With best regards

Olaf Kluge