In addition to anticipating the effects of a multirow data modification, trigger rollbacks, and trigger nesting, there are other factors to consider when you write triggers, such as permissions, restrictions, and performance.
In additional to the rules associated with triggers, consider these situations when you create triggers:
Suppose you have an insert or update trigger that calls a stored procedure, which in turn updates the base table. If the nested triggers configuration parameter is set to true, the trigger enters an infinite loop. Before executing an insert or update trigger, set sp_configure “nested triggers” to false.
When you execute drop table, any triggers that are dependent on that table are also dropped. To preserve any such triggers, use sp_rename to change their names before dropping the table.
Use sp_depends to see a report on the tables and views referred to in a trigger.
Use sp_rename to rename a trigger.
A trigger fires only once per query. If the query is repeated in a loop, the trigger fires as many times as the query is repeated.