<< FitNesse: Using COM+ Enterprise Services to Manage Transactions | Home | Rick Mudridge video presentation about FIT >>

Database Triggers Rant

The reasons I don't like database triggers:

1.) They are very difficult to debug
2.) They are a type of "magic" within a system. "Magic" is when something happens when I run code that I don't see in the code.
3.) They are not intuitive.

I find that database triggers get used in an organization when one or more of these things happen:
1.) A DBA has been tasked with writing application code
2.) The code is more difficult to change than it is to debug a database trigger
3.) The deployment process for code is significantly longer than the deployment process for SQL changes

posted @ Tuesday, February 07, 2006 7:57 PM

Print

Comments on this entry:

# re: Database Triggers Rant

Left by Matt R. at 5/3/2006 10:06 AM
Gravatar

Since I develop in Oracle, I tend to use Triggers along with Sequences to create my auto-incremental Primary Keys, since Oracle doesn't have anything built into the table like SQL Server 2000. But that is usually the extent of their use.

However, I can see some benefits to them, in terms of refactoring. For example, Scott Ambler gave an example in one of his books where using a trigger would come in handy if you are replacing a column with another column. The trigger would be used to keep the two columns in sync until all applications referencing the old column were pointed to the new column. Then you could drop the column from the table and remove the trigger.

# re: Database Triggers Rant

Left by Tim Gifford at 5/3/2006 9:10 PM
Gravatar

Matt,

You have some good points, especially the one about database refactoring and triggers. I would allow this since it doesn't contain business logic, it just provides a migration path to improved code.

As for sqequences...I am a proponent of using guid/uniqueidentifiers for key values over integers for a couple reasons:

- The business layer determines the key without requiring a round trip. This makes domain objects must easier to work with

- Migrating data across environments maintains consistent. I hate trying to migrate data to another environment only to discover that I have change my keys since the destination system is already using those values. Not only do I have to update the primary key, but I have to update any related foreign keys.

- Finally Portability. All databases all guids even if I have to store them withing a fixed length character field.

Comments have been closed on this topic.