Comparison of check constraints and BEFORE triggers
You must consider the difference between check constraints when considering whether to use triggers or check constraints to preserve the integrity of your data.
The integrity of the data in a relational database must be maintained as multiple users access and change the data. Whenever data is shared, there is a need to ensure the accuracy of the values within databases.
- Check constraints
- A (table) check constraint sets restrictions on data added to
a specific table. You can use a table check constraint to define restrictions,
beyond those of the data type, on the values that are allowed for
a column in the table. Table check constraints take the form of range
checks or checks against other values in the same row of the same
table.
If the rule applies for all applications that use the data, use a table check constraint to enforce your restriction on the data allowed in the table. Table check constraints make the restriction generally applicable and easier to maintain.
The enforcement of check constraints is important for maintaining data integrity, but it also carries a certain amount of system activity that can impact performance whenever large volumes of data are modified.
- BEFORE triggers
- By using triggers that run before an update or insert, values
that are being updated or inserted can be modified before the
database is actually modified. These can be used to transform input
from the application (user view of the data) to an internal database
format where desired. BEFORE triggers can also be used to cause other
non-database operations to be activated through user-defined functions.
In addition to modification, a common use of the BEFORE triggers is for data verification using the SIGNAL clause.
There are two differences between BEFORE triggers and check constraints when used for data verification:- BEFORE triggers, unlike check constraints, are not restricted to access other values in the same row of the same table.
- During a SET INTEGRITY operation on a table after a LOAD operation, triggers (including BEFORE triggers) are not executed. Check constraints, however, are verified.