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

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.

Sign in
to vote
ID 307937 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 11/1/2007 12:37:16 AM
Access Restriction Public


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.