Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Disable All Triggers Expand / Collapse
Author
Message
Posted Thursday, July 1, 2010 4:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:38 AM
Points: 2,379, Visits: 7,579
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


Not a DBA, just 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


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #946054
Posted Thursday, July 1, 2010 5:14 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, October 17, 2014 1:07 PM
Points: 1,264, Visits: 3,588
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.
Post #946083
Posted Thursday, July 1, 2010 5:16 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 11:33 PM
Points: 361, 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
Post #946084
Posted Thursday, July 1, 2010 5:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:07 AM
Points: 6,040, Visits: 8,322
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
Post #946089
Posted Thursday, July 1, 2010 7:39 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, August 22, 2014 4:56 AM
Points: 1,093, Visits: 2,617
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
Post #946178
Posted Thursday, July 1, 2010 7:52 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 9, 2014 6:19 AM
Points: 554, Visits: 1,199
Good question. Here's an alternative method as an FYI.

sp_msforeachtable "DISABLE TRIGGER ALL ON ?"

Post #946181
Posted Thursday, July 1, 2010 8:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, October 17, 2014 7:46 AM
Points: 5,364, Visits: 8,948
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
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
Post #946219
Posted Thursday, July 1, 2010 9:59 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Tuesday, December 7, 2010 12:55 AM
Points: 771, Visits: 504
Good question and discussion.

Thanks all.
Post #946293
Posted Thursday, July 1, 2010 10:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:03 PM
Points: 265, Visits: 122
This is nice informaiton to know. Keep up the good work.
Post #946309
Posted Thursday, July 1, 2010 2:34 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:21 AM
Points: 1,676, Visits: 1,757
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
Post #946476
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse