Managed triggers can do wonders!
Triggers, constraints and relationships are cool to implement business constraints in the data layer. Many people don’t bother to use them and try to implement business constraints in the application/business layer, but believe me, having them in database not only guarantee reliable data storage but also much easier manageability. Updating a thing in database can reflect changes in all modules accessing that data.
Due to their inherent limitations, one can’t implement powerful business rules, but having CLR in SQL Server 2005 and option to write triggers in CLS, one can do wonders. To give you an idea, here is one real world example.
Consider a service provider company running two distinct systems like inventory and accounting system. Consider some inventory items can only be issued to certain clients, e.g. DSL inventory items can only be issued to DSL clients, Wireless inventory items can only be issued to Wireless clients. As inventory system is separate and accounting system is separate, we didn’t have database level of business constraints. We have to trust the applications or the users of the applications who are accessing the systems that they will follow this simple convention, which sometimes don’t hold true and causes lot of manual work. Now with SQL Server 2005, we can develop a trigger which checks the client type using accounting system web service and items can only issued to relevant clients.
One can still implement extended stored procedures in SQL 2000. However, they are hard to develop and manage. One needs to manually copy the DLLs to the database system and request system administrator to set permissions and configure them before use. And if you are updating the already configured dll, the administrator even has to shutdown the SQL services, which is not very pleasant!