Just a day ago, I received question in email regarding my article SQL SERVER – 2005 Disable Triggers – Drop Triggers.
Question : How to disable all the triggers for database? Additionally, how to disable all the triggers for all servers?
Answer:
Disable all the triggers for a single database:
USE AdventureWorks;
GO
DISABLE TRIGGER Person.uAddress ON AdventureWorks;
GO
Disable all the triggers for all servers:
USE AdventureWorks;
GO
DISABLE TRIGGER ALL ON ALL SERVER;
GO
Reference : Pinal Dave (https://blog.sqlauthority.com), BOL-Triggers
28 Comments. Leave new
How would
DISABLE TRIGGER Person.uAddress ON AdventureWorks;
disable all triggers on a database? Person.uAddress is right there
Hi Pinal
We use SQL Server 2000. I created a trigger, trying to update a field after a record is inserted.
To disable all the trigger of current database.
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
–To execute the SP—
Exec [DisableAllTriggers]
Hi Manish,
Please help me HERE
I tried your proc for the disabling of triggers at database level.
I created this proc successfully,but while executing i got the error as below:
Msg 343, Level 15, State 1, Line 1
Unknown object type ‘tableSalesOrderDetailDisable’ used in a CREATE, DROP, or ALTER statement.
Msg 343, Level 15, State 1, Line 1
Unknown object type ‘tableSalesOrderHeaderDisable’ used in a CREATE, DROP, or ALTER statement.
Please advice thanks a lot
make sure you have a space after the single quote and disable on:
@tableName + ‘ Disable
You probably have a table named “tableSalesOrderDetail” and is being concatenated with the word “Disable” (no object exists with that name), should result in
“Alter table tableSalesOrderDetail Disable trigger all”, not
“Alter table tableSalesOrderDetailDisable trigger all”.
Hope it helps
Hi Helder,
Thanks a lot for your reply
after i left the space as you said..i am getting the following error.
Msg 4902, Level 16, State 1, Line 1
Cannot find the object “Store” because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 1
Cannot find the object “Vendor” because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 1
Cannot find the object “PurchaseOrderDetail” because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 1
Cannot find the object “PurchaseOrderHeader” because it does not exist or you do not have permissions.
Please help me ,thanks a lot.
Hi,
I guess the user you are using to execute does not have permissions to ALTER those tables (as the message states).
Either login with “sa” and execute the procedure, or grant the user you’re logging in with alter table permissions on those tables.
Be carefull if you have more than one database on the server, if you run the procedure “as is”,
it will disable all triggers for ALL tables on ALL databases.
Hope it helps
Hi Helder,
Thanks for help.I was run the SP for multiple other schema i madde a change in schema to dbo now it looks fine.
Thanks a lot for your responses
Hi manish,
I am getting this error.
Please help me
Msg 4902, Level 16, State 1, Line 1
Cannot find the object “Store” because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 1
Cannot find the object “Vendor” because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 1
Cannot find the object “PurchaseOrderDetail” because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 1
Cannot find the object “PurchaseOrderHeader” because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 1
Cannot find the object “WorkOrder” because it does not exist or you do not have permissions.
Msg 4902, Level 16, State 1, Line 1
Cannot find the object “SalesOrderDetail” because it does not exist or you do not have permissions.
Thanks a lot… Manish Kaushik!
hi Mr Dave
i’ve seen your articles and are great, i need to create a script which will calculate a value based on previous value i.e ID Depreciation
51004 0
54796 0
58585 -19079.347
62466 -113.57
66291 -113.57
70143 -113.57
74025 -113.57
77933 -113.57
81849 -113.57
on my script i need to calculate a Accummulative Depreciation on another field and the rule is on the first entry the Depreciation will remain the same, the second entry should take the value from the prevous entry add it to the current entry i.e for ID 51004 and 54796 AccumDepr will be 0 cause there’s no Depreciation value this id 58585 should equal to 19079.347 cause there’s no previous depr, and on ID 62466 my AccumDepr should be (-19079.347) + (-113.57) = -19192.91 and the next ID should be -19192.91 + -113.57 = -19306.48 and so forth…
Good Day! I’m a beginner with regards to triggers. Currently, I’ve already created a couple of triggers that are working just fine. But my problem is that one of our clients bought a new server. They want to transfer some databases in the new server and that would affect the triggers that are currently working. Is it possible for the trigger to work in different servers? What will be the syntax for that?
Thanks in advance.
Hi all,
To enable & disable all the triggers and constraints we have a system defined Stored Proc
Print ‘Disabling all Constraints’
exec sp_MSforeachtable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
Print ‘Disabling all Triggers’
exec sp_MSforeachtable ‘ALTER TABLE ? DISABLE TRIGGER ALL’
Print ‘Enabling all Constraints’
exec sp_MSforeachtable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
Print ‘Enabling all Triggers’
exec sp_MSforeachtable ‘ALTER TABLE ? ENABLE TRIGGER ALL’
I hope this helps.
Thanks
Vishal Kapoor
Hi Pinal,
I am a beginner. In an interview I have been asked how to know whether a trigger is working ? or how to know whether the trigger will be fired at the correct time?
can you please help me…
thanks in advance.
thanks alot mr Manish Kaushik
the Procedure DisableAllTriggers works well
But Could you let me add small notice :)
we have to Add [ and ] to the table name I tried to
exceute this procedure on my database and error occured
because of my table name [References] this is a key word
set @string =’Alter table [‘+ @tableName + ‘] Disable trigger all’
Hi Manish,
This query
select name as tbname from sysobjects where id in(select parent_obj from sysobjects where xtype=’tr’)
that you have used to retrieve the list of tables in the database is not correct.
The query you have provided will include tables and views and the Alter table statement will not work with a view.
I would use the following query to retrieve just the table names.
select table_name from information_schema.tables where table_type = ‘base table’
Thanks
Hi All ,
To enable and disable all the trigger we can directly use
DISABLE TRIGGERS ALL ON ALL SERVER ;
I love one line commands that do everything !
DISABLE TRIGGER ALL ON ALL SERVER;
Like this for indexes as well
sp_MSforeachtable @command1=”print ‘?’ DBCC DBREINDEX (‘?’)”
Cheers on all the great content
Keep in mind that this article is About DDL triggers, following script will generate sql statement for disabling triggers
SELECT ‘Disable Trigger ‘+name+’ On ‘+OBJECT_NAME(parent_id) FROM sys.triggers
is there any way on how to change the database name in all existing triggers on a database.
Simple method is to Script out the triggers and do find and replace
hi.Thanks for the response.
Any idea how i can do it, mean how to script out the triggers. I know you can select * from sys.triggers.
Hi to all.
To disable all DML triggers on all tables and VIEWS, i’ve improved Manish Kaushik’s procedure:
CREATE Procedure [dbo].[DisableAllTriggers]
AS
Declare @string varchar(8000)
Declare @tableName nvarchar(500)
Declare @triggerName nvarchar(500)
Declare cur cursor
for select s.name as tbname, t.name as trname FROM sys.triggers t
inner join sysobjects s
on (s.id = t.parent_id)
where s.xtype=’V’
exec (‘sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”‘
open cur
fetch next from cur into @tableName, @triggerName
while @@fetch_status = 0
begin
set @string =’DISABLE TRIGGER ‘+ @triggerName + ‘ ON ‘ + @tableName
print @string
exec (@string)
Fetch next from cur into @tableName, @triggerName
end
close cur
deallocate cur
go
Exec [dbo].[DisableAllTriggers]
go
drop procedure [dbo].[DisableAllTriggers]
go
Forgot to add the closing parenthesis on the line:
exec (‘sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”‘)
Hello Pinal Sir
i have a question i want to block drop table permission on database to specific user please suggest me the way i can achieve this please remember one thing user have access to alter but not drop table..
Amit Jha
its easy use trigger on that table or database..
Hi there !
I’m in a situation where i have to disable all my triggers and enable them in the and of my specific backup.
I kept a table #InitiallyDesabledTR(object_ID, Name) with the object_id and the name of all my triggers that are initially disabled.
Did any one have an idea on how could i re-enable all my triggers except those present on my table InitiallyDesabledTR.
Thanks for responding :)
I have created few Triggers on ALL SEVER
no how do I view a Trigger created on “ALL SERVER” using management studio or SQL Query.