Triggers

DML triggers are useful in these ways:

• They can cascade changes through related tables in the database; however, these changes can be executed more efficiently using cascading referential integrity constraints.
• They can guard against malicious or incorrect INSERT, UPDATE, and DELETE operations and enforce other restrictions that are more complex than those defined with CHECK constraints. Unlike CHECK constraints, DML triggers can reference columns in other tables. For example, a trigger can use a SELECT from another table to compare to the inserted or updated data and to perform additional actions, such as modify the data or display a user-defined error message.
• They can evaluate the state of a table before and after a data modification and take actions based on that difference.
• Multiple DML triggers of the same type (INSERT, UPDATE, or DELETE) on a table allow multiple, different actions to take place in response to the same modification statement.

Constraints and DML triggers each have benefits that make them useful in special situations. The primary benefit of DML triggers is that they can contain complex processing logic that uses Transact-SQL code. Therefore, DML triggers can support all of the functionality of constraints; however, DML triggers are not always the best method for a given feature.

• FOREIGN KEY constraints can validate a column value only with an exact match to a value in another column, unless the REFERENCES clause defines a cascading referential action.
• Constraints can communicate about errors only through standardized system error messages. If your application requires, or can benefit from, customized messages and more complex error handling, you must use a trigger.

• DML triggers can disallow or roll back changes that violate referential integrity, thereby canceling the attempted data modification.
• If constraints exist on the trigger table, they are checked after the INSTEAD OF trigger execution but prior to the AFTER trigger execution. If the constraints are violated, the INSTEAD OF trigger actions are rolled back and the AFTER trigger is not executed.

Disadvantages (Problems) of Triggers

• It is easy to view table relationships, constraints, indexes, stored procedure in database but triggers are difficult to view.
• Triggers execute invisible to client-application application. They are not visible or can be traced in debugging code.
• It is hard to follow their logic as it they can be fired before or after the database insert/update happens.
• It is easy to forget about triggers and if there is no documentation it will be difficult to figure out for new developers for their existence.
• Triggers run every time when the database fields are updated and it is overhead on system. It makes system run slower.

ALTER TABLE route_master DISABLE TRIGGER ALL