SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Disable All Triggers


Disable All Triggers

Author
Message
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3936 Visits: 8472
I try not to use undocumented procedures in anything other than a "one time" thing. I got it right, but personally I wouldn't have done it like that ;-)


Forever trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


Craig Wilkinson - Software Engineer
LinkedIn
calvo
calvo
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1818 Visits: 4009
I actually laughed a little when I saw the answers. I didn't know 'THE' was a valid t-sql command.

DROP THE TABLE <TableName>
CREATE THE TABLE <TableName>
DISABLE ALL TRIGGERS ON THE DATABASE

good question though, sp_msForEachTable is another tool in the arsenal.
thanks

______________________________________________________________________________________________
Forum posting etiquette. Get your answers faster.
hrvoje.piasevoli
hrvoje.piasevoli
SSC-Addicted
SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)

Group: General Forum Members
Points: 413 Visits: 510
Given the answer options, I agree that the sp_foreachtable option was the only correct one.

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.

But that does not make this the recommended method to disable all triggers in a database. As can be read in the documentation that is referenced in the explanation, a better way would be
DISABLE TRIGGER ALL ON DATABASE;

Not true. The command will ONLY disable all triggers scoped to DATABASE while TABLE and VIEW triggers will remain enabled.

Best regards,

Hrvoje Piasevoli

Hrvoje Piasevoli
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11132 Visits: 12004
hrvoje.piasevoli (7/1/2010)
But that does not make this the recommended method to disable all triggers in a database. As can be read in the documentation that is referenced in the explanation, a better way would be
DISABLE TRIGGER ALL ON DATABASE;

Not true. The command will ONLY disable all triggers scoped to DATABASE while TABLE and VIEW triggers will remain enabled.

Oops! You are right!
Thanks for the correction! :-)


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Richard M.
Richard M.
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1418 Visits: 2683
Good question and use of the undocumented sp_MSforeachdb.

Now, I have a remark here, the question stated "...disable all triggers in a database".

As Hrvoje noted above, Database scoped triggers as well as triggers on views would not be disabled.

_______________________________________________________________________
For better assistance in answering your questions, click here
Bradley Deem
Bradley Deem
Say Hey Kid
Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)Say Hey Kid (673 reputation)

Group: General Forum Members
Points: 673 Visits: 1248
Good question. Here's an alternative method as an FYI.

sp_msforeachtable "DISABLE TRIGGER ALL ON ?"


WayneS
WayneS
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10008 Visits: 10574
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

KevinC.
KevinC.
Right there with Babe
Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)Right there with Babe (785 reputation)

Group: General Forum Members
Points: 785 Visits: 504
Good question and discussion.

Thanks all.
Jan Sorenson
Jan Sorenson
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 125
This is nice informaiton to know. Keep up the good work.
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1785 Visits: 1814
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search