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.

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

• Prevent Unauthorized Index Modifications – SQL in Sixty Seconds #183
• SQL SERVER – Prevent Users from Changing Index
• SQL SERVER – Trigger on Database to Prevent Table Creation

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.

CREATE TRIGGER StopIndexCreation ON DATABASE
    FOR CREATE_INDEX,
    ALTER_INDEX, 
    DROP_INDEX
AS
PRINT 'This database does not allow new Triggers.'
ROLLBACK TRANSACTION
GO

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)