A community of more than 1,600,000 database professionals and growing

Today is the day for T-SQL Tuesday #106 entries. I'm the host this time, which means that I'll have a busy week trying to compile all the entries for the round up. Last week I wrote the invitation, which was on triggers. Actually, I wrote the invitation in August, trying to be prepared in case my August host fell through, but Wayne Sheffield did a great job, so my hosting duties went on as planned. You still have time to write a post today for the party, but if you read this later, write at anytime. T-SQL Tuesday is a great set of ideas for blog posts, if you're ever looking for an idea.

When I think of triggers, overall I think of the headaches they have caused me. Since I rarely see them in most systems, when they do appear, it's often because I can't determine why some strange action is occurring and after hours of troubleshooting, someone else will usually suggest checking a trigger. At least, that's been my experience in the past. These days when something unexpected happens, I tend to think trigger earlier, but I hate that they're kind of hidden. The DDL is separate from the table, the folder in SSMS isn't obvious. Really I wish that trigger folder was hidden unless there was a trigger. That would be nice.

In any case, my thoughts on this T-SQL Tuesday are actually a time when I thought a trigger solved a problem really well for me. In this case we had an OLTP application and a finance application. In the pre-SSIS, pre-DTS days, moving data between systems was cumbersome, and in this case, I needed to move over some data reliably from one to the other. We elected for replication, since that would solve our issues, but these were disparate systems, with different schemas, and more importantly, a schema were were not supposed to alter for the finance application. 

Enter loose coupling. I set up two additional databases, one on each instance. In these I had my tables that were to be replicated, and that worked really well, until it didn't. Even in the v6.5 days replication would sometimes flake. No problem, these are two small tables and I can reinitialize things easily. However, to get the data in and out of these tables, I decided to use triggers. We had an insert/update trigger that would take OLTP data and move it into the small publisher database on that system. On the other side, the subscriber database had a trigger to move data into the finance system. It worked well, and if we had replication issues, we could keep working, knowing that data would continue to stack up and we didn't need to alter our application.

I'm a fan of each system doing it's own thing, usually independently, and having processes that do one thing. I can stack those up, but I expect and count on failures at some point. Usually these are minimal, but I try to avoid a long set of things that might have cascading failures if one thing ceases working. In this case, even if a trigger failed or replication had issues, I could manually add some data to another table and it would get moved by subsequent processes.

Triggers are incredibly useful in places, but I do warn people to be careful. They always fire, and often we have exceptions we might need to deal with. We also need to be sure we easily handle multiple rows in every transaction. If you can remember that, and write tight, quick, small code, maybe you'll find a helpful use for triggers rather than one that gives you headaches.

Join the debate, and respond to today's editorial on the forums

DAX Formulas can contain up to 64 nested functions. 

Ref: https://docs.microsoft.com/en-us/power-bi/desktop-quickstart-learn-dax-basics 

» Discuss this question and answer on the forums