We’ve finally made it to the last post in the SQL103 series. I’ve written posts about triggers before. I’ve shared with you how triggers can be good things and bad things. It’s all in how you design them. Today, I hope to teach you how you can design good triggers. Let’s dive right in!

Understand There are Two Types of Triggers

There are two types of triggers available to you in SQL Server. You’ve got DML (data manipulation Language) triggers these are the triggers most people think of when you start talking about triggers. These triggers happen when you do an insert, update or delete (any query that would change the data in your database).

Then you have DDL (data definition Language) triggers, these fire when you make changes to the database itself. These can fire when you add, alter, or drop a table. These are useful, but you’ve really got to plan out how you want to respond to these actions before you start building.

You Want Triggers To Be Simple

When you design your triggers you want them to do simple things, usually a single DML statement. You do not want them to call a stored procedure that starts a job, that scans your largest table, then summarizes the data for every customer, and then write that to another server on the other side of the planet through a linked server connection. You have to remember to keep the body of your triggers simple, because whatever code you put into the trigger will fire every time the event happens that you’ve added that trigger on.

Understand the Trigger Fires For the Batch

One of the biggest mistakes I see in trigger design is they’re built under the assumption that only one record is hitting the trigger. You must never assume that! Always assume that you’re going to get more than one record firing your trigger. Get an idea of how many records are updated in each batch before you implement your trigger. Test the code you’re going to put into the trigger with batches of that size. Make sure your code runs quickly, if it doesn’t, you’re going to be slowing down your system when you add the trigger.

Speaking of Testing

If you’re adding an update trigger to a table, get a baseline on how long update statements take before you add the trigger. Then when you add your trigger, test it again. How much impact did your trigger make on the update times. If it’s noticeable, you want to revisit your code, and consider a change.

Look at the execution plan of your update before and after you add the trigger. See how it’s changed. Is the performance impact acceptable? If not, revisit your trigger code.

Don’t Avoid Triggers, Embrace Them!

If you’ve ever read any of the Star Wars novels that are set after Return of the Jedi, you know that Luke learns something very important about The Force, there’s no Dark Side or Light. There is simply how you use The Force. The Force itself doesn’t know good or evil. It’s up to the wielder. If someone uses some Force lightning to fire up an electric generator and keep a hospital full of sick children running, that’s not Dark Side, even though he used a Dark Side power to save the day.

It’s the same with triggers, they’re not good or evil. It’s all in the implementation. If you make sure your trigger does exactly what you need it to do (and nothing more), and you minimize your impact on performance, they can be very good things.

But avoiding triggers can be worse than using them. I’ve seen cases where application programmers put code into the user interface to handle archiving change data. The performance was horrible. I was actually able to help them add triggers to their database and speed up their application’s performance! Since we let the database do what it was best at doing, and then let the user interface code do what it was best at, the whole system sped up.

And that’s what database design is all about. Let the database do what it does best, and nothing more. Always delegate functionality when it makes sense.

Well, that’s it for database design. If you have any questions, send them in. I’m here to help. By the way, I’m going to be presenting this class at SQL Saturday #87, in Louisville, KY at the end of the month. Come and check it out!

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.

Search

What I Do…

Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

If you’ve seen my OnPrem to Azure presentation, then you know one of the two big reservations I had about windows azure was we couldn’t ...

Connect

Subscribe to our e-mail newsletter to receive updates.

Recommendations

Shannon is a dedicated and reliable team player. He is technically proficient and responsive to requests, always willing to help.

Nelson Cardella, Director of Development, Integration Point

Shannon is a very valuable asset to any development team, as one who is always looking ahead and trying to improve the status quo. Shannon’s work ethic is great, he is always willing to do anything in his power to get things accomplished for the team. Shannon doesn’t just become a team member who shows up to put in his 8 hours and go home… he gets emotionally involved and attached with the team, and treats them like family. I look forward to another opportunity to work with Shannon in the future.

John A. Cundiff Jr., Senior Systems Team

Shannon is a premier Database Engineer. His ambition is second only to his shining technical expertise. Spanning from pre SQL 2000 to SQL 2008 and into Azure, Shannon is on the cutting edge always consuming information to make his functional SQL top of the line and it shows. Shannon’s work ethic and SQL knowledge are rock solid. If you need someone to come in and streamline your SQL, gaining untold amounts of time on queries, saving processing overhead and pinpointing every issue that you may not have known you even had, then he is your man. I defer any and all of my SQL questions to Shannon and I have never been let down.