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

User · Answer

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'

User · Answer

Below is the simplest way  Try the code  ALTER  TRIGGER trigger name  DISABLE  That s it

User · Answer

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

User · Answer

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

User · Answer

The line before needs to end with a   because in SQL DISABLE is not a keyword  For example   BEGIN   DISABLE TRIGGER

User · Answer

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

User · Answer

use the following commands instead   ALTER TABLE table name DISABLE TRIGGER tr name  ALTER TABLE table name ENABLE TRIGGER tr name

User · Answer

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

[sql-server] Disable Enable Trigger SQL server for a table

Examples related to sql-server

Examples related to triggers