Trigger versus Constraints

When it comes to enforcing integrity, we have to get the right tool for the right job. Later in the Good Practices section, we will look at good and bad practices for triggers and talk about the right tools there. One of the issues is enforcing the different levels of integrity, one of which is domain integrity, enforced through CHECK constraints.

Enforcing domain integrity through CHECK constraints is not always possible. Foreign key constraints may also provide insufficient processing complexity and this is where a trigger fits in.

We will use a trigger instead of a CHECK constraint when the validation required is not against another column within the table or a value. So if we had an order total which checks that the total matched the order plus tax and shipping, which cannot be done as part of a CHECK constraint. Instead, we will have a trigger that fires when the order table is updated or inserted, and validates those specific columns. Validating against a column in another table could be done in a case where clients are given credit rating. When an order is being placed, a join to the credit rating table from the client table is placed and a check is made that the client has enough credit to place the order. We will compare the value in the client table's credit outstanding column with the level in the credit rating table, to ensure that it falls within acceptable bounds.

Finally, when working with a CHECK constraint, if an error occurs, we have no control over the message; this depends on the SQL Server. If we wished to have a custom error message, we will have to implement this through a trigger, but if it is possible to stick with the generic message, then do so.