Disable All Triggers

Question

Post reply

Disable All Triggers

Viewing 12 posts - 16 through 26 (of 26 total)

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

Bradley Deem SSCrazy Points: 2565 More actions · Answer 1

Good question. Here's an alternative method as an FYI.

sp_msforeachtable "DISABLE TRIGGER ALL ON ?"

WayneS SSC Guru Points: 95409 More actions · Answer 2

David in .AU (6/30/2010)
Only one thing to note, this stored proc is undocumented which means it may disappear suddenly after a service pack or in new versions.

Well, there is one other thing to note: this procedure utilizes a cursor to loop through the tables. If you're using this procedure, you're probably using this in an utility procedure, and this would be acceptable.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

KevinC. SSCommitted Points: 1570 More actions · Answer 3

KevinC.

SSCommitted

Points: 1570

July 1, 2010 at 9:59 am

#1187761

Good question and discussion.

Thanks all.

Jan Sorenson Mr or Mrs. 500 Points: 579 More actions · Answer 4

This is nice informaiton to know. Keep up the good work.

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 5

hrvoje.piasevoli (7/1/2010)
I agree with that completely, but it is not quite precise because triggers created on VIEW objects will remain enabled as the procedure loops only TABLE objects.

This would be a very good question if it was worded correctly. Given the answer options, the option to use sp_MSforeachtable was the closest to correct choice, so it was the one to choose. However, the question asked "Which of these will disable all triggers in a database?" Well, the answer is neither, because as Hrvoje and Wayne already pointed out, sp_MSforeachtable only iterates through the tables using the cursor loop. It does not iterate through views, which might have triggers defined on them. Additionally, there might be some DDL triggers in the database. If the question simply asked "Which of these will disable DML triggers on all tables in a database?" then it would be a very good question. As worded though, neither of the answer choices are sufficient to complete the task. As a matter of fact, considering the possibility of the triggers on views, there is no good way to disable DML triggers without mentioning their name. For example,

alter table [schema_name].[table_name] disable trigger all;

works well for any table, but the syntax is not suitable for views, so the syntax compliant with both tables and views should be something like this:

disable trigger [schema_name].[trigger_name] on [schema_name].[table_or_view];

If I need to quickly disable all triggers in the database then I use disable trigger syntax to take care of all tables and views and then append disable trigger all on database to take care of the DDL triggers (if any). Something like this will do the trick (without any cursor loops of course):

declare @sql varchar(max);

set @sql = cast(

(

select

'disable trigger [' + s.name + '].[' + tr.name + '] on ' +

s.name + '.' + o.name + ';' + char(10)

from sys.objects o inner join sys.schemas s

on o.schema_id = s.schema_id

inner join sys.triggers tr

on o.object_id = tr.parent_id

where o.[type] in ('U', 'V')

order by o.name, s.name

for xml path(''), type

) as varchar(max)

) + 'disable trigger all on database;';

exec (@sql);

go

Oleg

Kari Suresh Hall of Fame Points: 3712 More actions · Answer 6

[p]Thanks yaar.. good question..

To disable..

[/p]

DISABLE TRIGGER Person.uAddress ON Person.Address;

GO

[p]And to Enable triggers, we can use this query.. [/p]

ENABLE Trigger Person.uAddress ON Person.Address;

GO

KSB
-----------------------------
Thousands of candles can be lit from a single candle, and the life of the candle will not be shortened. Knowledge and happiness never decreases by being shared.” - Buddha

hrvoje.piasevoli Ten Centuries Points: 1377 More actions · Answer 7

If I need to quickly disable all triggers in the database then I use disable trigger syntax to take care of all tables and views and then append disable trigger all on database to take care of the DDL triggers (if any)

The order of execution may have to be reversed in the case of a "security" DDL trigger design patern (trigger that prevents altering db schema)

Hrvoje Piasevoli

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 8

hrvoje.piasevoli (7/2/2010)
If I need to quickly disable all triggers in the database then I use disable trigger syntax to take care of all tables and views and then append disable trigger all on database to take care of the DDL triggers (if any)
The order of execution may have to be reversed in the case of a "security" DDL trigger design patern (trigger that prevents altering db schema)

This is an excellent suggestion Hrvoje (and keen eye too :-)), thank you very much for pointing it out.

Oleg

appy SSC Enthusiast Points: 143 More actions · Answer 9

thanks buddy, it really helped me in my project.

Ernie Schlangen SSCrazy Points: 2382 More actions · Answer 10

Thanks! This post (with the edit) answered my question regarding why this was done in such an unnecessarily (in my original opinion) difficult fashion.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 11

quick and easy way to disable all triggers.

thx

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

TomThomson SSC Guru Points: 104773 More actions · Answer 12

nice question. But some of the wrong answers could have looked a little more realistic to make the right answer less obvious.

Tom