Enforcing Business Rules: Choosing Among INSTEAD of Triggers, Constraints, and AFTER Triggers
This is the final chapter that discusses techniques to enforce data integrity, and as a summary, you can propose which ways are recommended to enforce data integrity:
To uniquely identify every row, define a PRIMARY KEY constraint. This is one of the first rules to apply to designing a normalized database. Searching for values contained in a PRIMARY KEY is fast because there is a UNIQUE INDEX supporting the PRIMARY KEY.
To enforce uniqueness of required values in a column or group of columns, other than the PRIMARY KEY, define a UNIQUE constraint. This constraint does not produce much overhead because there is a UNIQUE INDEX supporting this constraint.
To enforce uniqueness of optional values (columns that accept NULL), create a TRIGGER. You can test this uniqueness before the data modification with an INSTEAD OF trigger, or after the data modification with an AFTER trigger.
To validate entries in a column, according to a specific pattern, range, or format, create a CHECK constraint.
To validate values in a row, where values in different columns must satisfy specific conditions, create one or more CHECK constraints. If you create one CHECK constraint per condition, you can later disable specific conditions only, if required.
To validate values in a column, among a list of possible values, create a look-up table (LUT) with the required values and create a FOREIGN KEY constraint to reference the look-up table. You could create a CHECK constraint instead, but using a LUT is more flexible.
To restrict values in a column to the values contained in a column in a second table, create a FOREIGN KEY constraint in the first table .
To make sure that every entry in a column is related to the primary key of another table, without exceptions, define the FOREIGN KEY column as NOT NULL.
To restrict the values in a column to complex conditions involving other rows in the same table, create a TRIGGER to check these conditions. As an alternative, create a CHECK constraint with a user-defined function to check this complex condition.
To restrict the values in a column to complex conditions involving other tables in the same or different database, create a TRIGGER to check these conditions.
To declare a column as required, specify NOT NULL in the column definition.
To specify a default value for columns where no value is supplied in INSERT operations, declare a DEFAULT property for the column.
To declare a column as autonumeric, declare an IDENTITY property in the column and specify the seed value and the increment.
To declare a default value, which depends on values in other rows or tables, declare a DEFAULT property for the column using a user-defined function as a default expression.
To cascade changes on primary keys to related fields in other tables, declare a FOREIGN KEY with the ON UPDATE CASCADE clause. Do not create triggers to perform this operation.
To delete in cascade related rows when the row in the primary table is deleted, declare a FOREIGN KEY with the ON DELETE CASCADE clause. Do not create triggers to perform this operation.
To cascade complex operations to other tables to maintain denormalized data, create individual triggers to execute this operation.
To validate INSERT, UPDATE, or DELETE operations applied through a view, define an INSTEAD OF trigger on the view.
Do not use RULE objects unless you want to define self-contained user-defined data types. It is recommended to declare CHECK constraints instead.
Do not use DEFAULT objects unless you want to define self-contained user-defined data types. It is recommended to declare DEFAULT definitions instead .
What's Next?
This chapter covered the creation and use of triggers as a way to enforce complex data integrity.
Chapter 10, "Enhancing Business Logic: User-Defined Functions (UDF)," covers user-defined functions, which can be used as part of the trigger definition and as an alternative to triggers, providing extra computing capabilities to CHECK constraints and DEFAULT definitions.
Chapter 12, "Row-Oriented Processing: Using Cursors," explains how to use cursors. This could be useful in some triggers to deal with multiple-row actions.
Triggers always work inside a transaction, and Chapter 13, "Maintaining Data Consistency: Transactions and Locks," covers specifically that: transaction and locks. There you can see the implications of modifying data through triggers and how to increase concurrency, preventing undesired blockings.