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 2, 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 2, 2010 12:30 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
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 2, 2010 8:38 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 11:58 AM
Points: 1,676, Visits: 1,758
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 5, 2010 4:47 AM
SSC-Enthusiastic

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

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

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 8:22 AM
Points: 1,623, Visits: 359
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 7, 2010 2:58 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:18 PM
Points: 17,824, Visits: 15,756
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
Post #948860
Posted Saturday, September 18, 2010 1:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:02 PM
Points: 7,801, Visits: 9,553
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