I have previously written article about SQL SERVER - Disable All Triggers on a Database - Disable All Triggers on All Servers. This is alternate method to achieve the same task.
Following article is sent by Manish Kaushik. I recommand all of you to read original article along with this article for complete idea.
CREATE PROCEDURE [dbo].[DisableAllTriggers]
AS
DECLARE @string VARCHAR(8000)
DECLARE @tableName NVARCHAR(500)
DECLARE cur CURSOR
FOR SELECT name AS tbname FROM sysobjects WHERE id IN(SELECT parent_obj FROM sysobjects WHERE xtype=‘tr’)
OPEN cur
FETCH next FROM cur INTO @tableName
WHILE @@fetch_status = 0
BEGIN
SET @string =‘Alter table ’+ @tableName + ‘ Disable trigger all’
EXEC (@string)
FETCH next FROM cur INTO @tableName
END
CLOSE cur
DEALLOCATE cur
GO
—-To execute the SP
EXEC [DisableAllTriggers]
Reference : Pinal Dave (http://www.SQLAuthority.com)
pinaldave
In the cursor, you may want to use the system view “sys.Triggers” to get a list of triggers in the context of the current database rather than the outdated SQL 2000 style of using sysobjects. sysobjects is only for backward compatibility with 2000 and should be avoided on any new development. At the very least - if you did not want to use sys.triggers then you should be using sys.objects on SQL 2005 and 2008.
If development is on a 2000 instance then sysobjects is the way to go in order to get a list of triggers.
For 2005 and 2008 you could use
SELECT QUOTENAME(OBJECT_NAME(parent_id)) AS tbName from sys.triggers
But this does not take into account tables that exist within other schemas other than dbo. You will just receive an error if you run that code on triggers within other schemas.
I think the above(DISABLE ALL TRIGGERS ON THE TABLE) can also be deone with ALTER TABLE statement also.
How about using this
sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”
sp_msforeachtable “ALTER TABLE ? ENABLE TRIGGER all”