SQL Server Home
MSFT-MSO: DISABLE TRIGGER statement not compiling within a SP, however it executes just fine outside
as Won't Fix
11/1/2007 12:37:16 AM
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.
SQL Server 2008 July CTP
Win2003 Enterprise Server (SP2)
Operating System Language
Steps to Reproduce
no steps, only scripts :-)
-- create the table first
CREATE TABLE Customer(CustomerID INT IDENTITY, CustomerName VARCHAR(255), UpdatedBy VARCHAR(255), UpdatedDate DATETIME)
-- create a (simple) TRIGGER on the above table
CREATE TRIGGER InsertCustomer_Trigger ON Customer FOR INSERT, UPDATE
DECLARE @CustomerID INT = (SELECT CustomerID FROM inserted)
UPDATE Customer SET UpdatedBy = SUSER_NAME(), UpdatedDate = GETDATE()
WHERE CustomerID = @CustomerID
-- create a procedure to insert data to the above table after disabling the trigger
CREATE PROCEDURE InsertCustomer_1 (@CustomerName VARCHAR(255))
SET NOCOUNT ON
ALTER TABLE Customer DISABLE TRIGGER InsertCustomer_Trigger -- disable the trigger
INSERT Customer(CustomerName) SELECT @CustomerName -- insert a record
ALTER TABLE Customer ENABLE TRIGGER InsertCustomer_Trigger -- enable the trigger back
-- the above code compiles just fine and I used ALTER TABLE to disable/enable the trigger
-- Lets now attempt to create the same code, but with only change "DISABLE TRIGGER" statement to DISABLE/ENABLE the triggers
CREATE PROCEDURE InsertCustomer_2 (@CustomerName VARCHAR(255))
SET NOCOUNT ON
DISABLE TRIGGER InsertCustomer_Trigger ON Customer
INSERT Customer(CustomerName) SELECT @CustomerName
ENABLE TRIGGER InsertCustomer_Trigger ON Customer
-- the above code throws the following compilation error
(local)(FAREAST\niyer): Msg 102, Level 15, State 1, Procedure InsertCustomer_2, Line 5
Incorrect syntax near 'DISABLE'.
(local)(FAREAST\niyer): Msg 156, Level 15, State 1, Procedure InsertCustomer_2, Line 7
Incorrect syntax near the keyword 'TRIGGER'.
DISABLE TRIGGER statement which is well documented in BOL is not even compiling within a SProc, however the same statement executes just fine outside the SProc.
I'd expect that the two documented methods (ALTER TABLE and DISABLE TRIGGER) of enable/disable trigger work both inside of a SProc and outside the SProc.
to post a comment.
Please enter a comment.
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.
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:
SELECT 1 SELECT 2 SELECT 3 SELECT 4
I would expect the DISABLE TRIGGER work too.
And lastly, do we have any documentation around this?
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.
to post a workaround.
Please enter a workaround.
© 2013 Microsoft