Trigger is Database Evil? Not Really..
Trigger is Database Evil? Not Really..
by : Kasim Wirama, MCDBA
Some people said that trigger should be avoided to whatsoever because it will slow down your database performance. Actually, I tell you that trigger has so many benefits, if you have good understanding when and how to use them properly, trigger will pay off its potential problem that might arise. I agree but don't agree to their opinion as well, it depends on data and query environment. After I know the internal engine works to handle trigger, I love trigger, and consider it as the feasible component solution beside stored procedure and functions. So before I agree and don't agree to unpleasant opinion about trigger, I would tell you what is how the internal SQL Server engine dealing with user defined trigger.
What is the difference in internal engine between SQL 2005 and previous version? SQL 2005 handle trigger especially after trigger in tempdb database rather in transaction log. From I/O characteristics, it improves performance because tempdb database give random access rather than sequential access that transaction log provides. You can enhance tempdb performance by adding number of data files that equals to number of processor, in this case dual processor not hyperthreading processor.
Is there any overhead that moves trigger load from transaction log to tempdb database? Yes, there is overhead, your tempdb database most likely get bigger, because there is architectural changes in tempdb on SQL Server 2005. Tempdb consists of 3 parts. They are user object store, version store object, internal object. Trigger in SQL 2005 uses version store object in tempdb and row versioning is handled in version store object, so even though you do not use row versioning in snapshot isolation level feature of SQL 2005, yet you still use row version in trigger.
Another thing is that I find about trigger is about pseudo table deleted and inserted, those tables actually don’t have indexes on it, if those tables joins to other materialized tables, and the materialized table don’t have indexes on join predicate, it would degrade performance. It is worse if there are a bunch of records with pattern (INSERT SELECT ... UNION ....UNION.....so forth) get executed. It means that your trigger handles multiple records in one operation. The performance is good when there are a few records, but not for a bunch of records. So limit the number of records that your trigger should handle. There is no certain threshold of number of records, it depends the complexity of the trigger.
Make sure you use your trigger only for validating data, not for implement modification operation inside after trigger. If you need modification automation, it is better to implement with instead of trigger. Validation data that couldn’t be handled by check constraint, can be handled by after trigger, for example validating data against multirow, or you need to implement Referential Integrity across database.
Hope this information will give you enough grasp what happened under the hood when a trigger is executed and what situations when trigger is considered useful.