I am considering rewriting part of an application's data model update methods to include support for registering to events coming from the database. Are there any reasons why this would be a bad idea? Should I limit myself to receiving events fired by CRUD operations, or could I program more of the business logic around event notifications? What might be the potential pitfalls?
Definitely use an async method. If you are on a Microsoft SQL platform check out a combination of
1) Change Tracking available SQL Version > 2008: http://msdn.microsoft.com/en-us/library/cc280462.aspx
and
2) SQL Service Broker, which you can register for events with. (1. actually uses 2. under the hood IIRC): http://msdn.microsoft.com/en-us/library/ms345108(SQL.90).aspx
If you have to revert to triggers definately keep the impact of the trigger low. For example create your own log of changes and process it from somewhere else. Don't write an update and find out who to information in the same trigger as the change itself. It will slow down your queries a lot.
There are also options depending on what data layer technology you use: NHibernate has the concept of interceptors, same on the Enterprise Framework side. But they do not run in the database, which has advantages and disadvantages.
HTH Alex