Storing a transaction sensitive constant in Firebird

firebird

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?

Best Answer

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.

Related Question