SQLServerCentral Article

Using DMO to Enable and Disable Triggers

,

These code samples

illustrate how to enable and disable all triggers in a database at one time - a

task not easily done otherwise! If you haven't use DMO before, I've posted two

articles that you may find informative - Introduction to

SQL-DMO and More DMO

 

If you wanted to do this using plain vanilla SQL

you would need to create a script that had one "Alter Table xx Disable

Trigger yy" statement per trigger. As written these should run in VBScript

or as an ActiveX job. If you would like to use them in VB you should set a

reference to the SQL-DMO object library so that you get statement completion and

can do strong data typing. One advantage to using code for a task like this is

you can easily modify it to only run on tables that start with 'A' or on

triggers that contact the word 'Insert' in their name.

 

Note (2/26/01):

Brian Knight has posted an

alternative that you may like better if you prefer TSQL to code! I've also

just posted a new DMO article demonstrating how

to do a restore

 

Enable Trigger

Dim oServer

Dim oTable

Dim oTrigger

dim DBName

'change this to your database name

DBName="PUBS" 

Set oServer = createobject("SQLDMO.SQLServer")

oServer.LoginSecure = True

oServer.Connect

For Each oTable In oServer.Databases(DBName).Tables

    For Each oTrigger In oTable.Triggers

        oTrigger.enabled=true

    Next

Next

oServer.DisConnect

Set oServer = Nothing

Disable Trigger

Dim oServer

Dim oTable

Dim oTrigger

dim DBName

'change this to your database name

DBName="PUBS" 

Set oServer = createobject("SQLDMO.SQLServer")

oServer.LoginSecure = True

oServer.Connect

For Each oTable In oServer.Databases(DBName).Tables

    For Each oTrigger In oTable.Triggers

        oTrigger.enabled=false

    Next

Next

oServer.DisConnect

Set oServer = Nothing

 

Rate

4 (1)

You rated this post out of 5. Change rating

Share

Share

Rate

4 (1)

You rated this post out of 5. Change rating