2

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:

  • Firebird 2.5.2 (SuperClassic)
  • FlameRobin 0.9.3
  • Delphi XE5 update2
Nick Udell
  • 2,306
  • 5
  • 41
  • 81
Lapio
  • 21
  • 2

2 Answers2

3

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.

Mark Rotteveel
  • 82,132
  • 136
  • 114
  • 158
0

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.

Borek Zanda
  • 1
  • 1