SQL SERVER – Database Trigger for Index Can Prevent Index Rebuild

Lots of people reached out to me after my previous blog post on database triggers. One of the most asked questions to me was why I do not recommend this method and I keep on suggesting one should use database roles. Well, the answer is very simple. Today we are going to discuss Database Trigger for Index Can Prevent Index Rebuild.

SQL SERVER - Database Trigger for Index Can Prevent Index Rebuild PreventIndex-800x125

Here are the blog posts which will give you the context of the topic.

The primary reason, I do not like to use database triggers is because they pretty much disable all the index operations for every single user on the database. As the matter of fact, it is so aggressive that it also does not allow to rebuild of the indexes. Here is a simple example.

First, let us create a database trigger.

PRINT 'This database does not allow new Triggers.'

Now let us run the following query to rebuild one of the indexes.

ALTER INDEX [NCI_Department] ON [HumanResources].[Employee] REBUILD 

When I run the query it gives the following error:

This database does not allow new Triggers.
Msg 3609, Level 16, State 2, Line 3
The transaction ended in the trigger. The batch has been aborted.

Well, you can clearly see database trigger is not the best possible solution as it also prevents index maintenance.

If you have any questions, please feel free to reach out to me on Twitter.

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

Exit mobile version