Variable constraint messages: Example 5

Example 5

It is also possible to refer to object names and column prompts in constraint messages.:

SELECT     o.id
,          c.name
,          ABS( c.credit )   debt
FROM       order o
,          customer c
WHERE      o.cust_id = c.id
AND        c.credit < 0

For this constraint, you can specify the message:

<o.id.objectName> <o.id.value> is rejected. <c.name.prompt> <c.name.value> has an outstanding debt of <debt.value> US dollars.

The message displayed might for instance be:

"Order 12455 is rejected. Customer Jones has an outstanding debt of 12,364.12 US dollars".

Although meta information about objectName and Prompt is constant, the maintainability of the messages increases when using this mechanism. Hence, changing a prompt name of a column will automatically have effect on the constraint messages in which this prompt is used. Only meta information defined in the Definer can be used.

For Domain Constraints object names and column prompts are useful, since this is variable information and there can be more than one column based on the same domain.