A few weeks ago I did some consulting work for a client who had a poor-performing SQL Server. From my title and first sentence, you know where this is going...
My client was in the process of migrating to a new application. Since they were migrating in stages, they needed to run both old and new applications concurrently. However, they wanted the data to stay in sync, so they used triggers to duplicate transactions between tables. As you can imagine, they had all kinds of locking, blocking and deadlock issues. So we created indexed views of all of the new tables mapping the old columns to the new. Now the old application was using the new structure and all of their data was in one place.
The point here isn't that we shouldn't use triggers. Triggers have their place. In fact, I’ve seen some really cool things done with triggers, cool things that didn’t crush servers. Having said that, I rarely use them because I usually can do what I need to do with a view or a proc.
Earlier this week I had to disable many triggers just so that we can Restore Databases and Create Databases
The triggers were set to send out alerts on DDL events but 2 issues
1. The DBA db didn't exist on that server but the trigger is relying on that db
2. SharePoint creating database name so long that it broke the XML column used in the DDL trigger for Creating Database
Triggers are bad here, especially with multiple applications. I had to do this once with an inventory/sales app and an accounting app. trigger->accounting caused issues as well even for a couple users.
My solution was to create two tables, one in each db, that contained the data I needed. We used a trigger in the sales app to copy data to the "shadow" table in that database and then replication to move that data to the other "shadow" table in the other database. A trigger then updated the accounting app.
That de-coupled solution worked very well.
