Creating an advanced constraint

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

Create a trigger on insert/update on the table that would contain your business rule.

Hm.. 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.

By witnessing its effects?!?


Data validation on user's side?

What I mean is that I would like to have some kind of synchronous communication... I.e. when a trigger is triggered, something changes in the DB, but I need to make an additional query to find this out. I know nothing of the changes, unless I issue a new SELECT statement. Whilst when I have a constraint, the DB explicitly notifies me that what I want to do is not allowed. And this is the kind of behaviour I want. Is it possible to achieve it with a trigger?




Hmm... this makes sense I just wanted to have the DB check this as well (i.e. in case someone insert a bad value in an installation script).

Maybe you're a little bit confused. Your trigger kicks in after or before a row (or several rows..) is inserted/updated/deleted in a table.
If a business rule (checked by the trigger) is not respected, the trigger enforces it -- by raising an error for example. [You can do practically anything inside a trigger: update other tables, call other global/package procedures or functions etc] Then you know that something went wrong: the DML statement failed with a specific error & message.
You don't need to issue a SELECT statement to find out whether a given operation failed or not. Trying to insert into a table an invalid value will fail due to various constraints imposed on the table(s) by the DB designer.
If all went well then... you really don't want to know. Just commit the transaction and be done with it.

As I said above, RDBMS checks all domain/referential integrity constraints before doing anything, then all before triggers are called, then all after triggers are called; if any of these operations fail a specific error is generated.
So a "bad value" could be the culprit in any of these failures.

Oh, ok If it works this way, then I guess it's all I need I will give it a check. Thanks a lot

Peter