Voice of the DBA

Tag: T-SQL Tuesday

  • The Trigger Roundup–T-SQL Tuesday #106

    This month was my turn to host T-SQL Tuesday. I chose Trigger Headaches or Happiness as the topic, and I am glad that there have been quite a few responses.

    I started the review almost immediately,and here are a few highlights. I separated these based on how I first thought of them after reading the post. If you think I’ve mischaracterized one, let me know.

    In case you don’t want to add, it’s 9 to 13, so triggers are a headache.

    Helpful Triggers

    I have to start with Burt Wagner, who gets top billing while wearing a Hawaiian Shirt. Thanks, Burt, and interesting solution. In the case where Burt wants to use Temporal Tables in pre-SQL 2016 instances, he uses triggers to manage the history table. I think that’s a great way to use triggers, and while slightly hidden, this should overall work well. The downside is that any schema changes to the table need changes to the trigger and history table, which could be slightly lost if developers don’t realize this.

    The newest evangelist at Redgate is Kendra Little, who loves triggers. Her post looks at her first experience with triggers. It was positive as a way to detect changes for downstream systems.

    Shane O’Neil has written about when triggers are good, talking about the way in which he tracks when new databases appear on his instance, with an email to auditing.

    Aaron Bertrand discusses INSTEAD OF triggers, and gives you the places where these are useful, or even necessary.

    I’ll file this as helpful when Peter Schott writes some code to disable triggers.

    Data migration is always tricky, but Jay Robinson gives a way in which triggers are helpful. I think that this is a great use, and more people ought to do this to simplify deployments and let data migrations occur over time, not all at once.

    Despite his misgivings, I think Marek Masko shows that he uses triggers effectively in code.

    I think Service Broken is amazing, and I wish that messaging and queueing processes were a pattern more of us used. Reid DeWolfe shows how triggers work in conjunction with Service Broker to get things done.

    A simple solution from Eugene Meidinger with the cautions that we need to be very careful with server level triggers.

    Headache Triggers

    Rob Farley has a discussion about some of the problems you can run into with triggers. He likes them, and has written about them before, but he offers advice for how to deal and write triggers in your organization.

    Dave Mason has a post on DDL triggers, which fire in response to event classes. If you’ve never used these, Dave gives you a few ideas on where they might be handy.

    We get a look at the good, bad, and ugly of a trigger in an AG from Bob Pusateri.

    David Fowler has a great post on nested triggers, with Service Broker in between to ensure there is no limit to the number of times the trigger fires.

    Jon Shaulis has a long post that provides an overview of triggers, where they work and don’t, and includes a few ideas for replacing triggers.

    Claudio Silva writes about triggers being hidden and wasting his time.

    I almost can’t believe the number of triggers Allen White has seen on a table, but since I have had similar customers and employers, I’m not surprised.

    Logon triggers can be headaches, and James Livingston shares one of his with us.

    The great Hugo Kornelis gives us a way to use triggers effectively and not be sad. Vote for a suggestion to make them more visible in SSMS. I voted, and I agree. We need better visibility.

    While Eduardo Pivaral writes about good and bad triggers, I think he feels triggers are somewhat bad overall. Certainly his “good fix” might not have been the best choice in his eyes, even though it worked.

    Not realizing that a trigger needs to operate on a number of records is a common mistake, so I’m putting Brian Dudley’s post in this area.

    I’d say that trigger anti-patterns fall into the headache area, and we get a few situations from Nate Johnson.

    Matthew McGiffen tells about the trouble with triggers. As with many of the other posts, he finds that triggers might be worth less than we initially think.

  • The Helpful Triggers

    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.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 4.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

  • T-SQL Tuesday #106 – Trigger Headaches or Happiness

    tsqltuesdaySince I took over the T-SQL Tuesday a few months ago, I decided I ought to host again. Especially since I’ve done it twice, but Wayne Sheffield got his third spot last month. Gotta keep up with Mr. Sheffield.

    Triggers, for fun and frustration

    I’ve been working with SQL Server and T-SQL a long time, and across many jobs, I think I’ve ended up using triggers in 0.01% of my tables or less. They can be a useful and helpful construct, but they can also be problematic and difficult, especially in the age of changing business models and rules.

    Since I’ve found triggers to be both helpful and hurtful, I decided to ask you to write about an experience you’ve had with triggers. Either good or bad, but let me know this month what stands out in your mind.

    The Rules

    As always, the rules for this month are simple.

    • Write and publish a post on September 11, 2018, UTC time.
    • Include the T-SQL Tuesday logo (you can grab this above) and link your post back to this invitation.
    • Leave a comment/pingback on this post for me to use to include you in the roundup
    • Have fun.

  • Another Brick in the Wall–T-SQL Tuesday #105

    tsqltuesdayIt’s that time again, and this month we have a good topic from Wayne Sheffield. We’re asked about getting stuck, about being blocked, about encountering a brick wall.

    I have to say that I don’t think I encounter these very often, usually because I try to be effective, even if things get ugly in code. The important thing for me is to deliver value. That doesn’t mean I build slapdash applications and leave them. I do my best, and try to continue to learn to build things better. However, in the heat of the moment, I might need to do something silly that I end up either rewriting later or having someone else rebuild the solution.

    With that…

    The Large Aggregate

    I once worked for a financial services company. This was decades ago, back in the age of spinning disks, SQL Server 6.5, and flip phones. We had a number of clients on our main application, but one large client that dominated our business.

    We needed to provide quarterly summaries of activity and portfolio performance for all clients, but for this main customer, they also wanted us to add monthly reports. They were actually their own management firm, but we provided them with back end services. Within a month, they would have millions of transactions that we had to group by some organization and then calculate performance.

    This wasn’t a big deal with other clients, though most of them had relatively simple calculations. For this client, however, they had separate rules for certain types of activity and the result was a calculation that we couldn’t easily get into CASE statements or other types of query workflows.

    In trying to handle all their requirements, I (and my junior team), got stuck. We couldn’t come up with a set of queries that would work. We didn’t want to go down the road of writing custom queries for each organization as this would be an ongoing nightmare as new requirements were introduced.

    We had delay after delay in trying to implement their monthly reporting and pressure kept mounting. Eventually we turned to using temp tables and making different passes through them to handle specific requirements. We had ugly flags in our temp tables to let us know which set of rules we’d applied and which we hadn’t.

    The code worked, but it was slow. Too slow, in fact, and my boss wasn’t pleased. After a particular unpleasant dressing down, I was wondering if this was the career for me. I’d talked with friends, posted questions, but in this pre-SQLServerCentral era, there were limited places to get assistance. I had hit brick wall.

    I never got to find a solution to the problems. My boss at the time was one of my worst, and he became petty. I started to get assigned various inane tasks, which were slightly annoying. However, on a night off, while on a date with my wife at a concert, he paged me with an emergency. I had to leave the show to answer and call in. When I did, he told me this was just a test to see if I would respond, then berated me for taking more than 5 minutes to do so.

    I quit the next day.

    Perhaps not the best reaction, but between the brick wall and poor treatment, I decided to move on. Hopefully someone else managed to solve the the performance issues and help the client.