TRIGGER BUG ??

[Oppenhiemer]

Does anyone know of a problem with Triggers in Interbase (version 6.x.) Apparently the bug means that triggers fire twice.

I am having this problem myself (my auto-inc fields are incrementing by 2 instead of 1) so would appreciate any help anyone can give on this.

Cheers..

Opp.

P.S Here is an example of the code IM using :

CREATE TRIGGER SET_NEW_TABLE_ID FOR TABLE1
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
new.ID = gen_id(GEN_TABLE_ID,1);
END

 

[Guest_imported]

Hello,

Try this SQL in interbase ou firebird:

create generator gen_yourtable;

set term !!;
create trigger yourtable_bi for yourtable active before insert position 0 as
begin
if (new.id is null) then new.id=gen_id(gen_yourtable,1);
end !!
set term; !!

Interbase:

http://www.borland.com

Firebird:

http://www.ibphoenix.com

 

[Oppenhiemer]

Your a star SolveProblems! . That has solved the problem. I was told that it may be because I had secured the database by removing the Source code - but that was obviously not the case.

Anyway - thanks again.

Opp.

 

[MartijnTonies]

It is caused by the deletion of the source.

However, with the suggestion, the triggers checks if the field has a value, if not, it calls GEN_ID (increment once) and if so, it doesn't.

Don't delete the source code by setting it to NULL - set it to some kind of text as "deleted for safety" or something.

Martijn Tonies
InterBase Workbench - the developer tool for InterBase and Firebird

http://www.interbaseworkbench.com

 

[Oppenhiemer]

Why then does it do the same thing (fire twice) on a new database where the source code has not been deleted ?

To test the deletion of code theory, I created a completely new database, a new table with a new generator. I created a test app using Delphi, created the appropriate data controls (Datagrid, IBDATASET with the GeneratorField property to the new trigger (GEN_NEW_ORDER_ID -> ID By 1.))

Low and behold, as soon as I tried to create a new record, it incremented by 2 - not 1 as specified in the trigger source. I am using the last released free version of INterbase from Borland (6.5 ?)

Here is the Trigger source I use that fires twice :

CREATE TRIGGER SET_ORDER_ID FOR ORDERS
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
new.ID = gen_id(GEN_NEW_ORDER_ID,1);
END


As you will notice, no check for a null value before it fires. In Delphi, the trigger is set to fire "On New Record". Which it does, only increments by 2.

Anyway, perhaps you can see something else Im doing wrong. Does deleting the source from another database somehow corrupt Interbase itself (and all subsequent Trigger definitions ?) To test this, I uninstialled Interbase removed all references to it in the registry, and manually deleted any system databases left on the comp. Doing this made no difference.


Opp.

 

[MartijnTonies]

Hi,

What makes you think the _trigger_ fires twice? If you use the "GeneratorField" property, IBX will get a value from a generator before it send the sql to the server.

By changing the trigger code to use "if new.thing is not null" - you're just checking to see if the client didn't supply a value. But it did (via GeneratorField) -> hence, the generator increments only once (from the client - not the trigger). Without the "not null" check, you're overwriting the value that the client gives you via the generatorfield property.

Martijn Tonies
InterBase Workbench - the developer tool for InterBase and Firebird

http://www.interbaseworkbench.com

 

[svanels]

Opp, did you code something in the on new record event? In general I leave the ORderID field in the table or query read-only to avoid conflicts Steven van Els
SAvanEls@cq-link.sr

 

[Oppenhiemer]

No Steven, I havent got anything tied in to a new record event. Martijn has solved the mystery, its a matter of how you use the "GeneratorField" property in Delphi - in conjunction with the trigger definition.

As Martijn pointed out, if you set the GeneratorField property to "On New Record", this will pass the increment value to the server. This in turn gets incremented by the amount specified in the trigger.

I tested the theory by making the increment value 5 in Delphi. and predictably, new record ID values went up in increments of 6! 5 for the value specified in Delphi and 1 as defined in the trigger.

If you change the properties of the Generator field to say "On Server" this problem goes away. As the new value is left to calculated by the server (trigger.)

Anyway, mystery solved ;-). Thanks to all that responded - especially to SolveProblems who got me out of the mess short-term and Martijn, for solving the true puzzle. Guess its down to me being a relative Noob with Delphi and Interbase.


Cheers..

Opp.