Home Dashboard Directory Help

MSFT-MSO: DISABLE TRIGGER statement not compiling within a SP, however it executes just fine outside by Narayan_Iyer


 as Won't Fix Help for as Won't Fix

Sign in
to vote
Type: Bug
ID: 307937
Opened: 11/1/2007 12:37:16 AM
Access Restriction: Public
User(s) can reproduce this bug


The following statement (syntax) is not compiling within a stored proc, however I'm able to execute just the DISABLE TRIGGER statement outside the SP just fine...

DISABLE TRIGGER <trigger_name> ON <table_name>

I know there is an alternate method to disable the trigger using ALTER TABLE, which works within a SP as well. I wonder why DISABLE TRIGGER statement, which is documented in BOL is not working within a stored proc.
Sign in to post a comment.
Posted by Microsoft on 1/3/2008 at 12:18 PM
That is true. It looks like I missed the important part in the previous explanation: currently "DISABLE" is not a keyword, and this is the reason why we require the previous statment to be ended with semicolon.
In order to fix this we would have to make "DISABLE" a keyword which might break users who have table, columns, functions etc. named "disable".
We'll look more closely at this and see if we can fix it without breaking users.
Posted by Narayan_Iyer on 12/19/2007 at 4:34 AM
I'm sorry I have to disagree.

I have never used ; (semicolon) as a terminator in any of my code for the past 6 years in SQL. :-)

All statements run OK with the exception of just this DISABLE TRIGGER, which makes me wonder why only this particular statement requires the previous statement to be terminated with a semicolon?

IF the following mult-statements in one line would work:


I would expect the DISABLE TRIGGER work too.

And lastly, do we have any documentation around this?
Posted by Microsoft on 11/5/2007 at 3:00 PM
The problem is that DISABLE TRIGGER is preceded by another statement in the batch and that statement is not ended with a semicolon (;). In order to fix this issue you have to end the previous statement (in this case SET NOCOUNT ON) with a semicolon (;), otherwise the parser cannot detect where the statement begins and when it ends. Please note that this would also happen outside of a stored procedure if there is another statement before DISABLE TRIGGER in the batch.
Sign in to post a workaround.