triggers and IDENTITY

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

triggers and IDENTITY

Omari Stephens-4
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/hsqldb-user
Loading...