Creating a more advanced constraint

[p***@gmail.com]

Hi!
I have a table (called TEST) which is a dictionary
NAME (VARCHAR) - VALUE (VARCHAR)

VALUE can be in practice of any type stored as a VARCHAR (I know it's not elegant but it's a kind of a DB equivalent of a system.properties file so I need to keep different types).

For NAME = 'A' VALUE is an SMALLINT.

And here's my problem:

When NAME='B', I want VALUE to be always greater than the VALUE for 'A'.
I tried creating such a constraint:

alter table TEST add constraint TEST_CK
check ( NAME <> 'B' OR (NAME = 'B' AND SMALLINT(VALUE) > (SELECT SMALLINT(VALUE) FROM TEST WHERE NAME='A')) );

but it turns out I cannot have subqueries inside constraints.
How to solve this?
Best regards,

Peter

[s***@computer.org]

@ Peter,

the logic that you've developed may be suitable for a database trigger that fires either BEFORE or AFTER the INSERT or UPDATE on a row in the dictionary table.

That is easy enough to implement if there are only two rows in the table, but most dictionaries that I use have many more rows. It may be something to think about as a design issue if you want this rule to apply to all rows in the table, having strictly increasing values.

[p***@gmail.com]

Hmm.. thanks :) One more question: how do I know whether the trigger has been triggered or not? I would like to provide some info to the user that the value they passed is too big. I suppose I could make the trigger modify something else I would have to check, but since I haven't got much experience in SQL, I'm not sure if this is the right approach.

[Knut Stolze]

Post by p***@gmail.com
Hmm.. thanks :) One more question: how do I know whether the trigger has
been triggered or not? I would like to provide some info to the user that
the value they passed is too big. I suppose I could make the trigger
modify something else I would have to check, but since I haven't got much
experience in SQL, I'm not sure if this is the right approach.

You could raise a warning condition.

[p***@gmail.com]

Could you give me an example of such a trigger? I'm not sure what you have in mind.