CODEWITHSUNDEEP
sessiontriggersdatabase-connectionfirebirdflamerobin
Lapio
Lapio

Reputation: 21

Firebird: Why does ALTER TRIGGER have no effect on other sessions/connections?

When I modify a trigger using the FlameRobin SQL editor, the changes have no impact on other sessions/connections: the trigger is still executing the old version.

How is this possible? How can I force the changes to affect all connections?

I do call COMMIT after ALTER TRIGGER, and there is no open transaction in the other connection either. This is not transaction related, but session/connection related.

"Other connections" are Delphi-applications (using InterBase-components), but the exact same behavior can be reproduced using two instances of FlameRobin.

I'm using:

Upvotes: 2

Views: 567

Answers (2)

Borek Zanda
Borek Zanda

Reputation: 1

Like Mark Rotteveel did suggest, you have to restart everything after each change. Or you have better choise: switch to SuperServer, which also has less bugs and memory leaks than SuperClassic. SuperClassic is crap.

Upvotes: 0

Mark Rotteveel
Mark Rotteveel

Reputation: 109239

In Firebird DDL is transactional, so a transaction that started before your transaction altered the database will see the state of the database as it was before your transaction did its work, only after a transaction of a database committed will it be able to see your changes.

On top of that there is a metadata cache. In a Classic and SuperClassic model each connection has its own caches (instead of a shared cache), and this per-connection-cache is not invalidated immediately (I don't know the exact details though). This means that statements that were prepared in a transaction before the change will work as if the database wasn't changed (this works unless the change fundamentally altered a table), while newly prepared statements will work with the changes.

Although DDL is possible when the database is in use, I'd suggest to do it with the database offline/single user, or force users to reconnect after executing the DDL to make sure all connections see and use the new changes.

Upvotes: 3

Related Questions