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 Friday, July 02, 2010 12:07 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, July 22, 2013 1:05 AM
Points: 248, Visits: 373

Thanks yaar.. good question..

To disable..


DISABLE TRIGGER Person.uAddress ON Person.Address;
GO

And to Enable triggers, we can use this query..


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
Post #946598
Posted Friday, July 02, 2010 12:30 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, October 19, 2013 12:40 PM
Points: 361, Visits: 508
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
Post #946603
Posted Friday, July 02, 2010 8:38 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 2:53 PM
Points: 1,676, Visits: 1,744
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
Post #946870
Posted Monday, July 05, 2010 4:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, August 01, 2013 11:07 PM
Points: 135, Visits: 26
thanks buddy, it really helped me in my project.
Post #947463
Posted Monday, July 05, 2010 5:52 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 25, 2013 7:37 AM
Points: 1,617, Visits: 355
Thanks! This post (with the edit) answered my question regarding why this was done in such an unnecessarily (in my original opinion) difficult fashion.
Post #947483
Posted Wednesday, July 07, 2010 2:58 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 11:47 PM
Points: 20,458, Visits: 14,082
quick and easy way to disable all triggers.

thx




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #948860
Posted Saturday, September 18, 2010 1:22 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 10:06 PM
Points: 8,283, Visits: 8,733
nice question. But some of the wrong answers could have looked a little more realistic to make the right answer less obvious.

Tom
Post #988771
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse