22.3. Check Constraints versus Triggers

For each of the individual datatypes, this chapter presents examples using both check constraints in table creation and insert and update triggers. Which is best depends on the datatype being implemented and other system considerations. Here are some considerations:

• Check constraints are far less verbose than triggers in terms of code.

• Check constraints are not PL/SQL procedures and thus have limited functionality.

• Other parts of the database may rely on triggers already or may have no triggers and already use check constraints.

• Besides the validation work required against the XML schema document, other validations may be required for the system. Triggers are a more natural fit because multiple triggers can be defined for the same event.

• The users/developers who will actually get the database error or need to easily interpret the error. Unlike check constraints, triggers are allowed to define the error message. Oracle provides a PL/SQL procedure RAISE_APPLICATION_ERROR to allow application-defined error messages and error numbers.

The important point is to decide on a set of guidelines during the database design process and enforce it during implementation. The examples in this chapter demonstrate the use of both, when applicable.