Post reply

Disabling a trigger

  • October 7, 2004 at 9:00 am

    #61164

    Hi!

    Tried to find out this from BOL, but could not.  Is there a way to disable a trigger with TSQL?

    Thanks.

     

  • October 7, 2004 at 11:52 am

    #525812

    It's buried in the ALTER TABLE command and can be hard to find.

    For all of them:

    ALTER TABLE tablename

    DISABLE TRIGGER ALL

    For a specific trigger:

    ALTER TABLE tablename

    DISABLE TRIGGER triggername

    K. Brian Kelley
    @kbriankelley

  • October 10, 2004 at 9:30 pm

    #526072

    Brian,

    Is there some undocumented - hard to find command to disable instead-of triggers on views - apart from dropping the trigger?

    Thanks,

    Win

  • October 11, 2004 at 3:38 am

    #526107

    I couldn't find a command to do that, but this proc will work.  Please forgive the terrible formatting - I can't persuade this post to be neat...

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_NULLS ON

    GO

    CREATE   PROCEDURE up_IOTriggerToggle @Table VARCHAR(500), @Enable BIT = 0

    AS

    Declare @Name varchar(100), @SQL varchar(1000)
    Select @Name = o.Name from Syscomments c join sysobjects o on o.id = c.id Where c.Id in

     (select ID from sysobjects where Parent_obj =

     (Select Id from sysobjects where name = @Table) and type = 'tr') 

      and Text like '%Instead Of Insert%As%'

    IF @Enable = 1

      set @Sql = 'alter table '+@Table+' enable trigger '+@Name

    ELSE

      set @Sql = 'alter table '+@Table+' disable trigger '+@Name

    print @Sql

    EXEC (@Sql)

    select @Name = o.Name from Syscomments c join sysobjects o on o.id = c.id Where c.Id in

     (select ID from sysobjects where Parent_obj =

     (Select Id from sysobjects where name = @Table) and type = 'tr') 

      and Text like '%Instead Of Update%As%'

    IF @Enable = 1

      set @Sql = 'alter table '+@Table+' enable trigger '+@Name

    ELSE

      set @Sql = 'alter table '+@Table+' disable trigger '+@Name

    EXEC (@Sql)
    Select @Name = o.Name from Syscomments c join sysobjects o on o.id = c.id Where c.Id in

     (Select ID from sysobjects where Parent_obj =

     (Select Id from sysobjects where name = @Table) and type = 'tr') 

      and Text like '%Instead Of Delete%As%'

    IF @Enable = 1

      set @Sql = 'alter table '+@Table+' enable trigger '+@Name

    ELSE

      set @Sql = 'alter table '+@Table+' disable trigger '+@Name

    EXEC (@Sql)
     
    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

  • October 11, 2004 at 4:11 am

    #526111

    Steven,

    Thanks for the proc, let's hope there will be a command in Yukon

    Win

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply