Disable Enable Trigger SQL server for a table

Question

use the following commands instead:

ALTER TABLE table_name DISABLE TRIGGER tr_name

ALTER TABLE table_name ENABLE TRIGGER tr_name

The line before needs to end with a ; because in SQL DISABLE is not a keyword. For example:

BEGIN
;
DISABLE TRIGGER ...

As Mark mentioned, the previous statement should be ended in semi-colon. So you can use:

; DISABLE TRIGGER dbo.tr_name ON dbo.table_name

After the ENABLE TRIGGER OR DISABLE TRIGGER in a new line write GO, Example:

DISABLE TRIGGER dbo.tr_name ON dbo.table_name

GO
-- some update statement

ENABLE TRIGGER dbo.tr_name  ON dbo.table_name

GO

I wanted to share something that helped me out. Idea credit goes to @Siavash and @Shahab Naseer.

I needed something where I could script disable and re enable of triggers for a particular table. I normally try and stay away from tiggers, but sometimes they could be good to use.

I took the script above and added a join to the sysobjects so I could filter by table name. This script will disable a trigger or triggers for a table.

select 'alter table '+ (select Schema_name(schema_id) from sys.objects o 
where o.object_id = parent_id) + '.'+object_name(parent_id) + ' ENABLE TRIGGER '+ t.Name as EnableScript,*
from sys.triggers t 
INNER JOIN dbo.sysobjects DS ON DS.id = t.parent_id 
where is_disabled = 0 AND DS.name = 'tblSubContact'