Disabling and enabling triggers

, 2019-03-20 (first published: )

I tend to feel that a lot of people who use triggers don’t really understand them. That said, every now and again you have to deal with them. And in particular (for this post) you might need to disable and then re-enable them. Enabling and disable are identical commands in this case so I’m just going to use the DISABLE version and you can just replace it with ENABLE as needed.


A single trigger

Usually if you need to disable a single trigger it’s for something like a load, or maybe you just don’t need it anymore and feel like creating some zombie code.

DISABLE TRIGGER Schema.TriggerName ON Schema.TableName;

All triggers for a table

This is going to be for pretty much the same reasons, although the load reason will be more often and the zombie code less often.

DISABLE TRIGGER ALL ON Schema.TableName;

As a side note you can get a list of all triggers on a table by using one of the following:

EXEC sp_helptrigger TS_Membership;
SELECT object_name(parent_id) AS parent_name, * FROM sys.triggers
WHERE parent_id = object_id('TableName');

You’ll notice that they each provide slightly different information, and of course the system view provides more than the sp_help function.

Now, any time you are disabling a group of triggers and there is any chance you will need to enable them again I HIGHLY recommend checking them out ahead of time to see if any of them are already disabled. When you are done you probably want to leave things the way they were when you started right? No bringing that zombie code back to life accidentally.


All triggers for a database

The only time I’ve ever seen this done is when you have to do a full reload of the DB and don’t want to mess with a bunch of triggers that are going to be changing data or loading log tables that you are going to be pulling from the source location anyway. As I said above, if you are going to do this you ABSOLUTELY (can’t say that often or strongly enough) need to check if any of them are disabled, make a list, and re-disable them when you are done enabling all of them.

Why not only enable the ones you care about? Well, you can, but it’s probably easier to mass enable then disable the few that you need to stay disabled.

EXEC sp_msforeachtable 'DISABLE TRIGGER ALL ON ?';

Ok, a bunch of you are looking at me funny going But we were told not to use those sp_msforeach… stored procedures? And yea, it is undocumented, don’t use it in any production code. But this is a situation where no one is going to be in the database (you aren’t making mass changes in a database with people in it are you?), and you are going to double check that you hit everything when you are done. Or, if you want you can use Aaron Bertrand’s (b/t) replacement for sp_msforeachdb as a template and build your own.


Database level triggers (DDL triggers for example)

If you look at these, particularly the all version you might think this a simpler way to disable all of the triggers in the database. It’s not. It disables database scoped triggers. i.e. DDL triggers. Not DML triggers which are scoped to tables etc.

One

USE dbname;
DISABLE TRIGGER TriggerName ON DATABASE;

All

USE dbname;
DISABLE TRIGGER ALL ON DATABASE;

Server level triggers (Logon triggers for example)

Same as database level triggers. These commands only affect server scoped triggers.

One

DISABLE TRIGGER TriggerName ON SERVER;

All

DISABLE TRIGGER ALL ON SERVER;

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads