triggers and IDENTITY

Background: I'm using hsqldb for a project.  I have a table of
subscriptions, which I am retrofitting.  I want to add the concept of
active and inactive subscriptions.  I want to enforce the constraint that
active subscriptions must be unique, and inactive subscriptions may be
non-unique (to enable a copy-edit-activate workflow)

Subscriptions table also includes "COLUMN id INTEGER GENERATED ALWAYS AS
IDENTITY"

My brilliant, somewhat problematic plan is as follows:
— create an "active" column, type boolean
— create an "idUnlessActive" column, type integer
— create a UNIQUE constraint including idUnlessActive, plus all the columns
that actually define the subscription.
— create two BEFORE triggers (INSERT and UPDATE):
IF newRow.active = TRUE THEN
  SET newRow.idUnlessActive = NULL;
ELSE
  SET newRow.idUnlessActive = newRow.id;
END IF;

The fatal flaw that I've run into is that newRow.id isn't defined until the
row is actually INSERTed.  Because the BEFORE triggers fire before the
INSERT, happens, I no longer have a UID to use, and the plan falls apart :o(

Any suggestions on (1) how to fix this so it does what I want, or (2)
better plans on how to enforce the unique-unless-inactive constraint
mentioned above?


It appears that hsqldb silently enforces the rule that AFTER triggers
cannot alter the table.  One thing I have tried is to create an AFTER
trigger which causes a no-op UPDATE to the same row, and then I have a
BEFORE UPDATE trigger which can successfully run my logic.  That said, I
have not yet figured out  away to trigger an UPDATE after each INSERT on
that table.  This table is not very hot, so doing an extra UPDATE for every
INSERT would be acceptable from a performance perspective.


Another alternative that comes to mind is just to keep active and inactive
constraints in two separate tables.  I would really like to avoid that,
because then the subscription UID would change whenever it's "active" bit
was flipped.

TIA,
--xsdg

------------------------------------------------------------------------------

_______________________________________________
Hsqldb-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user