I was helping someone out with something so I thought you guys would like to have these 2 short lines of code that can be pretty useful.  Here I’m going to show you how to check your DB for tables that have triggers, and then for tables with no indexes.  Do with them what you will.

First, let’s talk about triggers and how to avoid them and why.

Triggers are a tricky thing to support becuase the complicate things.  They can be nested and you won’t even realize it.  Their firing order isn’t even guaranteed.  You can specify the first trigger to fire, but no ordering after that.  So you could see intermittent perf problems from both of these aspects of triggers.  And the reasons I usually see people putting triggers on tables aren’t even valid.  They need something to happen in another table like a audit or maybe they even need to change a value in the current row.  That’s the one that drives me crazy the most.  If you need to update a value you just inserted, then just change it before you insert it.  Inserts should be as fast as possible and putting an update inside a trigger for a row you just inserted is just stupid.  Put that code in an SP and do whatever you need in params so by the time you get to the insert everything is as it should be.

And if you need to do something in another table, like auditing that an action took place, then again put that code in an SP and just do the 2nd table write after the main insert.  This really isn’t hard to figure out.  In my career I think I’ve seen maybe a couple honest uses for triggers and I can’t even remember them right now.  But pretty much every time you need a trigger, you would be better served by putting several statements into a single transaction in an SP.  At least someone could easily see what’s going on and you can tightly control the order of ops.

So to find all the tables in your DB with triggers, just go to the tables node in PS and run the following code.  Once you get it you can do anything with it you like.

dir | ?{$_.Triggers -ne $NULL} | ft schema, name, triggers -auto

Easy, right?

Now, if you do a get-member you’ll see a handful of properties that give you info on triggers.

dir | gm

HasAfterTrigger
HasDeleteTrigger
HasInsertTrigger
HasInsteadOfTrigger
HasUpdateTrigger

And of course you can use those, even for what we’re trying to do here, but it makes the code a little long to be practical.  The problem is you have to compare all of those properties. 

dir | ?{$_.HasAfterTrigger -eq $True -OR $_.HasDeleteTrigger -eq $True -OR $_.HasInsertTrigger -eq $True -OR $_.HasInsteadOfTrigger -eq $True -OR $_.HasUpdateTrigger -eq $True} | ft name, triggers -auto

Both of these lines of code do the same thing, but one is just less tedious than the other.

And you don’t have to filter it at all.  You’re free to just look at the raw data if you like:

dir | FT schema, name, triggers -auto

Ok, that’s it this time.