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?
A:
Sounds like you're considering to react to database events in your business logic. If so, this indeed would be a very bad idea, ignoring almost everything that is usually considered good software design (Layering, Separation of Concerns etc.)...
Thomas Weller
2009-10-03 10:11:21
It is not uncommon for applications to allow users to set watches on particular items, so they get notified when the data is changed. There are systems (usually of a sensitive nature) which issue notifications when other people look at data items, or even search for them. There are other systems with autonomic processes that monitor database events (for instance the SO badge subsystem).
APC
2009-10-03 10:43:08
If something is not uncommon this does not mean that something is good...But anyway: Of course I did not mean that the business logic should not react to persistence-related events, but that it is VERY bad design to couple the business logic that tightly to a specific database technology. There should be some sort of abstraction between business logic and the physical database.
Thomas Weller
2009-10-03 11:23:10
+2
A:
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%28SQL.90%29.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
AlexDuggleby
2009-10-03 11:16:31