Comparing triggers and constraints and triggers have an advantage in special circumstances. The main benefit of the trigger is that they can include complex processing logic using Transact-SQL code. Therefore, the flip-flop can support all functions of the constraint; but it is not always the best way to functionally given.
Entity integrity should always be enforced by indexing at the lowest level, or part of the primary key and unique constraints or independently outside the constraint. Suppose the functionality can meet the functional requirements of the application, and the domain integrity should be forced by the Check constraint, and reference integrity (RI) should be mandatory through the Foreign Key constraint.
The trigger is extremely useful when the function supported by the constraint cannot meet the functional requirements of the application. E.g:
Unless the REFERENCES clause defines a cascading operation, the Foreign Key constraint can only be verified by a value that matches the value in another column.
The Check constraint can only verify the column values according to the logical expression or another column in the same table. If the application requires the column values according to the column values in another table, the trigger must be used.
Constraints can only pass errors by standard system error messages. If the application requires the use of (or can benefit) custom information and more complex error processing, the trigger must be used. The trigger can achieve cascade changes through related tables in the database; however, these changes can be performed more efficiently through a level incidence integrity constraint.
The trigger can disable or roll back the changes to the reference integrity, thereby canceling the attempted data modification. This type of trigger may work when the foreign key is changed and the new value does not match the primary key. For example, you can create an insertion trigger on Titleauthor.title_ID, so that it is inserted back in a new value does not match a value in titles.Title_ID. However, it is usually used to achieve this purpose using Foreign Key.
If there is a constraint on the trigger table, check these constraints before the INSTEAD OF trip is executed but check these constraints before the AFTER trigger is executed. If the constraint is destroyed, the Roller INSTEAD OF trip operation does not perform the AFTER trigger.
