July 1, 2010 at 7:52 am
Good question. Here's an alternative method as an FYI.
sp_msforeachtable "DISABLE TRIGGER ALL ON ?"
July 1, 2010 at 8:40 am
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
July 1, 2010 at 9:59 am
Good question and discussion.
Thanks all.
July 1, 2010 at 10:15 am
This is nice informaiton to know. Keep up the good work.
July 1, 2010 at 2:34 pm
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
July 2, 2010 at 12:07 am
[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
July 2, 2010 at 12:30 am
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
July 2, 2010 at 8:38 am
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
July 5, 2010 at 4:47 am
thanks buddy, it really helped me in my project.
July 5, 2010 at 5:52 am
Thanks! This post (with the edit) answered my question regarding why this was done in such an unnecessarily (in my original opinion) difficult fashion.
July 7, 2010 at 2:58 pm
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
September 18, 2010 at 1:22 pm
nice question. But some of the wrong answers could have looked a little more realistic to make the right answer less obvious.
Tom
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply