Disable Enable Trigger SQL server

Question

I want to create one proc like below but it has error on syntax. Could anyone pointing out the problem?

Create PROCEDURE [dbo].[my_proc] AS

BEGIN

DISABLE TRIGGER dbo.tr_name ON dbo.table_name

-- some update statement

ENABLE TRIGGER dbo.tr_name  ON dbo.table_name

END

** Error Message : Incorrect syntax near 'ENABLE'.

Answer 1

use the following commands instead:

ALTER TABLE table_name DISABLE TRIGGER tr_name

ALTER TABLE table_name ENABLE TRIGGER tr_name

Answer 2

As the incorrect syntax is near 'ENABLE', are you sure it's not the update statement which is causing the problem?

If you haven't already, check the update statement in a separate query window so that you can eliminate it from being the cause of your error.

In my time working with T-SQL, when a syntax error shows up, it can sometimes be on the line or statement directly above where it is flagging the problem.

Answer 3

The line before needs to end with a ; because in SQL DISABLE is not a keyword. http://connect.microsoft.com/SQLServer/feedback/details/307937/msft-mso-disable-trigger-statement-not-compiling-within-a-sp-however-it-executes-just-fine-outside for example:

BEGIN ; DISABLE TRIGGER ...

Answer 4

AS Mark mentioned the previous statement should be ended in semi-column. So u can use:

; DISABLE TRIGGER dbo.tr_name ON dbo.table_name