How do I disable all the triggers at once in a DB
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: How do I disable all the triggers at once in a DB

  1. #1
    Join Date
    Dec 2000
    Posts
    87
    Hi,

    I'd like to disable 'all' the triggers at once in a schema for data loading, how can I do it without issuing the following for every single table?

    ALTER TABLE xxx
    DISABLE ALL TRIGGERS;

    Thanks.

  2. #2
    Join Date
    Mar 2001
    Posts
    45
    There is an internal table called user_triggers

    this will hold all the triggers pertains to your schema.

    just build a sql like this

    select 'alter table '|| table_name||' disable all triggers ; '
    from user_triggers;

    spool this output to a file and execute.
    Once bulk loading is complete,

    build the same script to enable it.

    that's it.
    Thanx
    Ramesh.
    ______________________________
    There is nothing Impossible.
    Even Impossible says
    I M POSSIBLE

  3. #3
    Join Date
    Feb 2001
    Posts
    184
    Hi,

    Here is a script that can be used without any Damage.

    spool Disable.sql
    select 'Alter Trigger '||Trigger_Name||' Disable ;'
    From User_Triggers;
    spool off

    @Disable.sql

    To Enable Use this one
    spool Enable.sql
    Select 'Alter Trigger '||Trigger_name ||' Enable ;'
    From User_Triggers;
    spool off

    @Enable.sql

    Hope this will help you.

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width