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!