SQL SERVER – Enable or Disable All Triggers

I do not like triggers at all and in my life, I have never written a trigger for my business logic. Recently, I had a client for  Comprehensive Database Performance Health Check who had lots of performance problems. After investigation, we figured out it is due to many triggers on their key tables. As soon as we did disable all triggers on the tables, we were able to see a huge performance improvement.

Here is the script which can enable or disable all triggers on your server.

Disable All Triggers

DISABLE Trigger ALL ON ALL SERVER;

Enable All Triggers

ENABLE Trigger ALL ON ALL SERVER;

If you are facing performance issues with your SQL Server, I would suggest that you try out this option of disabling all the triggers on your server, I am confident that it will give your SQL Server a performance boost. However, you should try this out first on your developer server because if you do this on a production server, you may start having data integrity issues.

SQL SERVER - Enable or Disable All Triggers disablealltriggers-800x555

Often people ask me what is the replacement of the triggers, my answer is simple, include your workflow of what you need in the trigger in your T-SQL code or Stored Procedure. Ideally, if you plan well, there is no need for triggers.

Let me know if you are interested to know more about this topic. I will write more blogs as well as create an SQL in Sixty Seconds video.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

, ,
Previous Post
SQL SERVER – Function to Calculate Simple Interest
Next Post
SQL SERVER – List Service Broker Queue Count

Related Posts

Leave a Reply

Menu