Disable Enable Trigger SQL server for a table

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

score 273 · Accepted Answer · answered Sep 07 '09 at 07:54

273

use the following commands instead:

ALTER TABLE table_name DISABLE TRIGGER tr_name

ALTER TABLE table_name ENABLE TRIGGER tr_name

answered Sep 07 '09 at 07:54

Wael Dalloul

22,172
11
48
57

4

what versions of SqlServer is this good for? not working for me, while `DISABLE TRIGGER [dbo].[tr_name] on [schema].[table_name]` worked – Maslow Jul 14 '16 at 21:11
2

Your answer is correct. But actually @pang statements don't need any fix instead of a simple `;`! I prefer to use `ENABLE Trigger`. It's applicable on all SQL Servers starting with 2008. – ABS Jan 13 '18 at 08:47
1

If you need to do all the tables in a database use this: EXECUTE sp_msforeachtable "ALTER TABLE ? disable trigger ALL" go – John Dyer Oct 29 '18 at 20:15

score 80 · Answer 2 · edited Mar 22 '16 at 03:04

80

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

BEGIN
;
DISABLE TRIGGER ...

edited Mar 22 '16 at 03:04

David Gardiner

16,892
20
80
117

answered Aug 30 '10 at 21:28

Mark Kane

801
6
2

12

I much prefer this answer. It addresses the problem and gives the solution instead of a workaround. While workarounds have their place, it is important to understand why an error occurred instead of blindly following a workaround with no context. – Bpainter Oct 16 '15 at 15:46

score 14 · Answer 3 · edited May 04 '15 at 14:57

14

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

edited May 04 '15 at 14:57

Guillermo Gutiérrez

17,273
17
89
116

answered Oct 02 '10 at 00:47

kaptan

3,060
5
34
46

score 4 · Answer 4 · edited Nov 09 '17 at 21:28

4

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

edited Nov 09 '17 at 21:28

C B

1,677
6
18
20

answered Nov 09 '17 at 17:36

Andrea

41
1

score 1 · Answer 5 · edited Oct 22 '18 at 13:09

1

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

edited Oct 22 '18 at 13:09

Siavash

2,813
4
29
42

answered Oct 22 '18 at 11:52

Shahab Naseer

11
1

score 1 · Answer 6 · answered Feb 16 '21 at 14:51

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'

score 0 · Answer 7 · answered May 24 '21 at 22:07

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'

score 0 · Answer 8 · answered Oct 05 '22 at 20:20

0

-- 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] = 1 -- Must be 1, not 0 -- --AND DS.[name] = 'TableName'

answered Oct 05 '22 at 20:20

maku

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Oct 09 '22 at 21:08

score 0 · Answer 9 · answered Dec 15 '22 at 18:54

Buffer States:

Pinned: Currently used. Clean: Meaning the buffer is now unpinned and is a candidate for immediate aging out if the current (data blocks) are not referenced again. The contents are either in synch with disk or the buffer contains a CR snapshot of a block. Free/Unused: Meaning the buffer is empty because the instance just started. This state is very similar to Clean, except that the buffer has not been used. Dirty: Buffer is no longer pinned but the contents (data block) have changed and must be flushed to disk by DBWn before it can be aged out. Three Buffer Pool:

Keep Recycle Default Database Buffer Cache Parameter:

DB_CACHE_SIZE (BLOCK SIZE will be the size declare in DB_BLOCK_SIZE) DB_KEEP_CACHE_SIZE DB_2K_CACHE_SIZE DB_4K_CACHE_SIZE DB_8K_CACHE_SIZE DB_16K_CACHE_SIZE DB_32K_CACHE_SIZE DB_RACYCLE_CACHE_SIZE DB_BLOCK_SIZE(Default Block size which can not be changed later anyway)

SHOW PARAMETER DB_BLOCK_SIZE;

show parameter db_cache_size;

score -1 · Answer 10 · edited Nov 19 '15 at 07:14

-1

Below is the simplest way

Try the code

ALTER TRIGGER trigger_name DISABLE

That's it :)

edited Nov 19 '15 at 07:14

Gunaseelan

2,494
5
36
43

answered Nov 19 '15 at 05:44

kavita londhe

17

Not working on SQL Server > 2008 R2 and the question is for MS SQL Server – kuklei Jul 04 '18 at 13:28

score -1 · Answer 11 · edited Dec 04 '19 at 07:00

-1

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"

edited Dec 04 '19 at 07:00

Avinash Dalvi

8,551
7
27
53

answered Dec 04 '19 at 06:06

ar3003

9
1

Disable Enable Trigger SQL server for a table

11 Answers11

Linked