There are several types of triggers.

• Logon triggers – Fired when someone tries to connect to the instance.
• DML triggers – Fired after a DML statement. INSERT, UPDATE and DELETE statements.
• DDL triggers – Fired after a DDL statement. For example CREATE, ALTER, DROP, GRANT, DENY, and REVOKE.

All triggers require careful thought before implementing them. Logon triggers in particular are dangerous because they can stop anyone from being able to connect to the instance and it can take some extra work to get around them if they run amok. DML triggers are next on my be careful when using list. The problem is that these guys fire the most frequently. INSERT, UPDATE, and DELETE statements are the lifeblood of an OLTP system, coming only only behind SELECT statements in frequency and importance. Because of this any problem with a DML trigger can be compounded dramatically. They’ve been around for a while and they are heavily documented so there are lot’s of examples on how to get this type of trigger right and we don’t see a lot of absolute failures. The bane of the DML trigger is performance. A small performance issue in a DML trigger can bring a system to a grinding halt.

So now we come down to DDL triggers. These are the least dangerous type of trigger. Of course this is kind of like saying my $10k server is cheap. It’s all about perspective right? DDL triggers generally only affect developers and DBAs not users. I guess it could affect a user, but if your users are running CREATE/ALTER statements for example, then you are probably doing something wrong. Performance issues aren’t really an issue here either. No one cares if a CREATE TABLE statement takes an extra 30 seconds or so. DDL triggers are newer and while they are still reasonably well documented they are more complicated (more commands can be covered) and so mistakes are easier to make. The bane of the DDL trigger is the unexpected code path that causes your trigger to fail. The real down side is that if you aren’t the one who wrote the trigger it can be a real pain to track down why your reindex process is failing. (No, I’m not bitter at all. No really, I actually enjoy that kind of thing. But I’m rather weird.)

So here is my call to action. We have drivers licenses. How about we create a trigger license? You have to pass a test before you are allowed to put a trigger into production. And since DDL triggers are less dangerous (at least by comparison) and so most likely to be used carelessly let’s start with those.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication Tagged: ddl triggers, dml triggers, logon triggers, microsoft sql server, triggers