At 10:30 AM 26/09/2009, you wrote:

>Hi,
> I have an application written in java, which saves data into firebird
>database (classic 1.5.3) .
> I needed extend the database a little bit.
> The application modifies one field in a table. I wanted to save the
>previous value of the field.
> So I added a field in the table old_value
> and created simple before update trigger:
>
> as begin
> new.old_value = old.myfield;
> end;
> When I change value of myfield in Ibexpert the trigger works and old_value
>is updated.
> Surprisingly the same operation through java application behaves
>differently:
> value of myfield changes but old_value stays unchanged.
> It seems that the trigger is not fired! Is it possible to deactivate
>triggers
> on connect individually for connection ?

No. Client applications cannot do that without performing an explicit, committed ALTER TRIGGER statement *after* connection has been established; and then, only by a user that has ownership privileges on the table that own the trigger.

What you are observing has some different explanation.

> I also checked the possibility that the value is not changed but whole
>record
> is deleted and inserted with old pk value - i have created before insert
>trigger
> firing exception.

That cannot happen, either. An updated record exists only in the transaction space until commit or rollback. The only way a client application can influence the outcome is to commit the transaction, or roll it back.

> And as previous I can not insert any new record throug Ibexpert
> but the application still ia able to change the value of myfield.

Under some transaction conditions, you won't be able to insert a new record if an uncommitted update is waiting. In this situation, you would get a lock conflict exception.

> Maybe you can explain me what is happenig ?

You can work through this yourself by looking at the transaction settings you are using for IBExpert and your Java app, respectively. Particularly, note the WAIT/NO WAIT policy that is in effect.

Know too that a new version of a trigger or SP is not available if an older version already exists in the cache, even if the original trigger body was just a "stub" like
...
as begin

end;

On Superserver, the database (not the server) would need to be shut down to clear the cache and free the way for the new version of the trigger. On Classic, each connection has its own individual cache, so detaching and re-attaching will be enough. The evidence suggests you are working with a Classic server and thus will need to do this detach/re-attach for the Java application each time you alter a trigger through IBExpert's connection.

Also, unless MyField is constrained to be NOT NULL, I recommend that you enhance the trigger to handle the situation where old.MyField is null, viz.

as begin
if (old.myfield is not null) then
new.old_value = old.myfield;
end;

As a testing mechanism, you might like to go a step further in altering this trigger, viz. something along these lines,

as begin
if (old.myfield is not null) then
new.old_value = old.myfield;
else
new.old_value = 'Old value was null;
end;

./heLen