I'd like to store a number in my Firebird database that indicates the revision of its structure. The number is supposed to be updated only when the structure has successfully been altered by some migration script. Therefore the storage mechanism needs to be transaction sensitive, which is why I cannot use a generator. The alternative I considered was to define a stored procedure which returns the number, and alter its definition in a transaction, but that as well seems to survive a rollback. What options do I have, not using a table?
I think you can use Database trigger which fires on
TRANSACTION COMMIT. However, you need to react only to those commits caused by the "upgrade script" so you need to set up an flag to indicate it. For that you could set an context variable in
USER_TRANSACTION namespace. Ie your script would look like this
RDB$SET_CONTEXT('USER_TRANSACTION', 'IncrementVersion', 1); -- other statements ... -- in the end of the script commit COMMIT WORK;
Now the database trigger can check for the
IncrementVersion context variable and act accordingly
CREATE TRIGGER IncrementDBVersion ACTIVE ON TRANSACTION COMMIT AS BEGIN IF(RDB$GET_CONTEXT('USER_TRANSACTION', 'IncrementVersion') IS NOT NULL)THEN BEGIN -- increment the version number GEN_ID(GEN_DB_Version, 1); -- just in case clear the context variable RDB$SET_CONTEXT('USER_TRANSACTION', 'IncrementVersion', NULL); END END
Here trigger uses generator (aka sequence) named "GEN_DB_Version" to "store" the version number.
If you use
COMMIT WORK statements in your script you have to make the system more complicated, either set up additional flags to ignore those commits in the middle of the script or set the flag up right before final commit or perhaps some third way - it all depends on what "side effects" you want to use.