Oracle Scripts - dbapool.com

dbapool: Authors | Submissions | Contact Us

Forgot password? | Sign up

Home

Discussion Forum

Articles

Interview Questions

FAQs

Scripts

Rewards

Analyzer

White Papers

Blog

Certification

Downloads

Tools

Procedure To ENABLE/DISABLE Triggerson all Tables in a Schema

By: S K Jun 20 2007

This procedure Enables or Disables the triggers on all the tables in the schema its currently being executed. It can be used as a sql script to run it any schema

rem :
rem Filename:   enabledisabletrig.sql
rem Purpose:    Procedure To ENABLE/DISABLE Triggerson all Tables in a Schema
rem Author:      Srinivasan Krishnan, dbapool
rem Date Submitted:20-Jun-2007
rem Date Published:20-Jun-2007
rem -----------------------------------------------------------------------

CREATE or replace procedure enable_triggers as
       v_tablename  varchar2(100);
       v_query1 varchar2(100);
       v_query2 varchar2(100);
       v_query  varchar2(100);
  
   -- A cursor is used to fetch all the Tables in the Schema    
       cursor v_cursor is
       select table_name from User_tables;
      
       v_row  v_cursor%rowtype;
      
       begin
      
       open v_cursor;
       loop
       fetch v_cursor into v_row;
                 if v_cursor%notfound then
                 exit;
                 end if;
                
              v_tablename := v_row.table_name;
             
              v_query1:=\'alter table\';
    
     -- For disabling the triggers on the tables ,
     -- the following line can be changed as \" v_query2:=\'disable all triggers\';\"
            v_query2:=\'enable all triggers\';
              v_query:=v_query1||\' \'||v_tablename||\' \'||v_query2;
              dbms_output.put_line(v_query); 
              execute immediate (  v_query);
             
              end loop;
             
              close v_cursor;
              end;
       /
      
   
    --- Execute the Procedure ------
    EXECUTE ENABLE_TRIGGERS;

Home Discussion Forum FAQs Articles Jobs Newsletters Directory Downloads

Our Premium Sponsor