SQL Tips by Namwar Rizvi

August 20, 2007

ENABLE/DISABLE all triggers of all tables in one statement

Filed under: Shortcuts,tips,Undocumented features,Utility Functions — namwar @ 8:15 PM

Sometimes we need to disable triggers to perform some tasks. Disabling the trigger by going to each table is very tedious. Following is avery quick way of disabling all triggers on all tables of the given database in a single statement.

sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”

To enable all triggers, you can use following statement

sp_msforeachtable “ALTER TABLE ? ENABLE TRIGGER all”


1 Comment »

  1. Not working for me 😦

    SQL> alter table ? disable trigger all;
    alter table ? disable trigger all
    ERROR at line 1:
    ORA-00911: invalid character

    Comment by Chirag — December 13, 2008 @ 12:58 AM | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at WordPress.com.

%d bloggers like this: