CODEWITHSUNDEEP
sql-servertriggers
pang
pang

Reputation: 4154

Disable Enable Trigger SQL server for a table

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'.

Upvotes: 161

Views: 266333

Answers (9)

Jay Walker
Jay Walker

Reputation: 11

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'

Upvotes: 1

kaptan
kaptan

Reputation: 3149

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

Upvotes: 16

Francisco J. Naranjo
Francisco J. Naranjo

Reputation: 11

USE [DatabaseName]
GO

-- HABILITAR TRIGGERS
SELECT 'ALTER TABLE ['+ 
    ( SELECT SCHEMA_NAME(SCHEMA_ID) FROM [sys].[objects] AS O WHERE O.[object_id] = T.[parent_id]) 
        + '].[' + OBJECT_NAME(T.[parent_id]) + '] ENABLE TRIGGER '+ T.[name] + ';' AS [EnableScript], *
        FROM [sys].[triggers] AS T 
            INNER JOIN [sys].[sysobjects] DS ON DS.[id] = T.[parent_id]
        WHERE T.[is_disabled] = 0 
            --AND DS.[name] = 'TableName'

-- DESHABILITAR TRIGGERS
SELECT 'ALTER TABLE ['+ 
    ( SELECT SCHEMA_NAME(SCHEMA_ID) FROM [sys].[objects] AS O WHERE O.[object_id] = T.[parent_id]) 
        + '].[' + OBJECT_NAME(T.[parent_id]) + '] DISABLE TRIGGER '+ T.[name] + ';' AS [EnableScript], *
        FROM [sys].[triggers] AS T 
            INNER JOIN [sys].[sysobjects] DS ON DS.[id] = T.[parent_id]
        WHERE T.[is_disabled] = 0 
            --AND DS.[name] = 'TableName'

Upvotes: 1

ar3003
ar3003

Reputation: 39

if you want to execute ENABLE TRIGGER Directly From Source :

we can't write like this:

Conn.Execute "ENABLE TRIGGER trigger_name ON table_name"

instead, we can write :

Conn.Execute "ALTER TABLE table_name DISABLE TRIGGER trigger_name"

Upvotes: -1

Shahab Naseer
Shahab Naseer

Reputation: 11

Below is the Dynamic Script to enable or disable the Triggers.

select 'alter table '+ (select Schema_name(schema_id) from sys.objects o 
where o.object_id = parent_id) + '.'+object_name(parent_id) + ' ENABLE TRIGGER '+
Name as EnableScript,*
from sys.triggers t 
where is_disabled = 1

Upvotes: 1

Andrea
Andrea

Reputation: 81

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

Upvotes: 8

Mark Kane
Mark Kane

Reputation: 841

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

BEGIN
;
DISABLE TRIGGER ...

Upvotes: 84

kavita londhe
kavita londhe

Reputation: 17

Below is the simplest way

Try the code

ALTER TRIGGER trigger_name DISABLE

That's it :)

Upvotes: -1

Wael Dalloul
Wael Dalloul

Reputation: 22984

use the following commands instead:

ALTER TABLE table_name DISABLE TRIGGER tr_name

ALTER TABLE table_name ENABLE TRIGGER tr_name

Upvotes: 294

Related Questions