quote:

---

I'll reiterate that triggers are less efficient than foreign keys at enforcing referential integrity. However, there are some situations in which AFTER triggers can offer value in this area. One example is when a business rule exists that requires a complex referential integrity check where a value in a table must reference a value in one of two parent tables. For example, assume that there is a tblBankAccount table and tblBrokerageAccount table, both of which have a column called nAccountNumber. This nAccountNumber field is defined as a CHAR(10) in both tables and is each table's primary key.
Now, assume there is a third table called tblTransaction which contains a required field called nAccountNumber. Normally, a foreign key constraint would be placed on this field to its parent table, but in this case there are two parent tables. This type of complex referential integrity can be validated using an AFTER trigger that checks to make sure that any value inserted or updated into the tblTransaction.nAccountNumber column is either a valid account number from tblBankAccount or tblBrokerageAccount. A foreign key constraint can only match one or more columns between two tables, while an AFTER trigger, despite being less efficient than a foreign key, has more possibilities. In this case the slight performance reduction might be less important than the gain you realize by enforcing the business rule.

---