SelecTViews
With Stephen Wynkoop
Mongo for Windows Phone SDK available, SQL Injection in the Cloud, an Interview with Sherri McDonald about SSRS and training coming soon
[Watch the Show]
Compensating for Bad Schema – Triggers
Triggers are a great asset to any DBA. They allow a database to react to modification of data through Insert, Update or Delete actions. They operate behind the scenes, extending the intentions of work being accomplished when modifying data.
Classic uses of triggers are for assuring data integrity. Some engines utilized triggers prior to having declarative referential integrity. Foreign key were enforced by use of a trigger. Triggers have even been used to assure data accurace when a check constraint could not be defined.
Triggers continue to be essential today; even with more specific tools for managing your database. Triggers have been used to communicate data change between systems. Some developers add triggers to third party products to communicate with other systems when data is modified. For internally developed systems, triggers are often used during transition periods enabling old and new software to co-exist.
Because triggers are so powerful, they are easy to abuse. I have seen triggers used to replicate data, denormalize data, normalize data, send Email,transform data, and much more. There are valid reasons to do most of these activities.
So how can they be abused?
- When the triggers become so complicated that the trigger execution hampers the need to modify the data on the underlying table
- When business logic is difficult to follow because it is hidden behind numerous triggers
- When triggers are being used to transform data (on a permanent basis) into a form that could already be known
- When a trigger is performing an action that has nothing to do with database persistence
All of these things are not bad within themselves. What never ceases to surprise me is the number of systems I’ve had to rescue from performance problems, infested with triggers, doing so much that a simple update in a table causes a chain of execution consuming huge resources with little or no value.
Back to the point…the majority of the time when I see trigger sprawl, it is caused by bad schema design. The data doesn’t look the way we want or need it, so we use a trigger to modify it as the data is entered. The more you use triggers to resolve you schema problems, the more complicated your system becomes to understand.
We’ll talk about views tomorrow.
Send your comments to btaylor@sswug.org.
Cheers,
Ben
$$SWYNK$$
Featured White Paper(s)
Sharepoint Adoption
Written by AvePoint
Microsoft SharePoint is quickly becoming the platform of … (read more)