Время выполнения триггера зависит от количества таблиц, указанных в триггере, и количеству записей, обрабатываемых во время запуска триггера. Поэтому всегда старайтесь уменьшить количество запрашиваемых таблиц и используемых записей в триггере.
Если триггер выполняет SELECT из таблиц, не забудьте включить необходимые индексы. Про запросы SELECT внутри триггера легко забыть. Однако использование индексов, как и для обычных запросов SELECT, может значительно повлиять на производительность. Одним их способов проверить это является запуск кода триггера в Query Analyzer и проверка результирующего плана выполнения. Это достаточно быстро поможет разобраться - нужно добавлять индексы или нет.
Если вы замечаете что операции INSERT, UPDATE или DELETE занимают дольше времени чем ожидается, то проверьте - используются ли триггеры для этой таблицы. Проблемы производительности могут возникать как раз из-за триггеров, а не из-за модификации данных самих по себе. Не забывайте настроить код триггера так же как и любой другой запрос. Так как триггер "скрыт", многие просто забывают о нем и не могут понять в чем может быть источник падения производительности. Вы можете использовать Profiler и Query Analyzer для того чтобы разобраться как триггеры действуют в вашей базе данных.
Не используйте триггер чтобы реализовать ссылочную целостность. В SQL Server встроена поддержка ссылочной целостности. Использовать ее намного лучше и быстрее чем заставлять триггер делать то же самое.
Если у вас есть выбор - использовать триггер или ограничение CHECK для реализации бизнес правил, то лучше всего использовать CHECK. Это ограничение работает быстрее чем триггер, проверяющий то же самое.
Старайтесь чтобы код триггера был минимальным. Это очень важно особенно для OLTP приложений, когда триггеры срабатывают во время операций INSERT, UPDATE или DELETE. Чем больше кода исполняет триггер, тем медленнее срабатывают операции модификации данных.
Избегайте делать откат транзакции в триггерах, так как это вызовет значительные издержки. Вместо этого пытайтесь откатить транзакцию еще до триггера, если ваш код позволяет это сделать. Подобный способ обработки транзакции займет намного меньше системных ресурсов. Обработку ошибок можно переложить в код запускающий транзакцию. Иногда достаточно добавить ограничение CHECK на таблицу. Если ограничение сработает и вызовет ошибку, то триггер не запустится.
Для реализации каскадной ссылочной целостности (например, каскадного удаления) в базе данных SQL 2000 используйте встроенную каскадную ссылочную целостность вместо триггеров, это более эффективно.
Иногда, для повышение производительности, нужно хранить денормализованные данные. Например, вам необходимо содержать агрегированные значения в таблице, иначе их расчет во время выполнения запроса занял бы длительное время. Одним из простых способов поддержки денормализованных данных является использование триггеров. Например, при добавлении записи в таблицу продаж SALES срабатывает триггер, который заносит данные в таблицу суммарных значений SalesTotal.