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 recommend 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 (https://blog.sqlauthority.com)
12 Comments. Leave new
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”
You can use
However note that they are all undocumented and may be removed from future release of SQL Server
sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”
sp_msforeachtable “ALTER TABLE ? ENABLE TRIGGER all”
This one is good, as written by rohit
How do we get the altered table name, time of altered, using trigger.
Try using DDL trigger
Ok, so i have disabled the triggers, how does enable it back using the above script?
— Include the schema:
DECLARE @string VARCHAR(8000)
DECLARE @tableName NVARCHAR(500)
DECLARE @Name NVARCHAR(500)
DECLARE cur CURSOR
FOR SELECT sys.objects.name as tbname, sys.schemas.name FROM sys.objects INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id
WHERE sys.objects.object_id IN(SELECT parent_obj FROM sysobjects WHERE xtype=’tr’)
OPEN cur
FETCH next FROM cur INTO @tableName, @Name
WHILE @@fetch_status = 0
BEGIN
SET @string =’Alter table ‘+ @Name +’.’ +@tableName + ‘ Disable trigger all’
EXEC (@string)
FETCH next FROM cur INTO @tableName, @Name
END
CLOSE cur
DEALLOCATE cur
GO
Hi – I searched the blog and other sources but could not find a way to do this:
Disable all the triggers on a specific table when a specific user is doing a specific maintenance task within the context of a .NET application.
Any ideas gratefully received…
Thanks
Jerry
Also pleas always surround your table names with brackets. This helps those of us dangerous enough to use reserved words as table names and table names with spaces
This is a very good script and running fine in demo version database that I have created but it is not working in my project database as i think because after disable procedure runs it is showing no of database tables by running the script in which the tables set comes with the no of triggers with the values disabled with “NULL”. Please solve my problem there very bunch of triggers exist.