Triggers may have uses, but they are verboten in my shop because of the maintenance difficulties you mention. We are a small dev shop with no DBA. Somebody mentioned data-specific uses such as auditing. If we had those requirements we would use a third party tool.

As for the "dumb database" issue, I am certainly an advocate, especially for a dev team such as ours. There are many whiz-bang advanced features of SQL Server, but I do not consider anyone in my company competent to implement or support them. I also agree that systems where the business logic is in the middle tier make them more scalable, but I also think it makes them more maintainable. I have worked on both kinds, and dumb database systems are less atomic and easier to understand.

However, I also believe that dumb databases should have lots of standard and normal constraints. Databases without key and foreign key constraints are not dumb databases, they are stupid databases