SQL Script Enable all database triggers

Updated : 1-Apr-2002
Version : 2.0

Description

This script enable all database triggers. If you are supporting a database that uses triggers, you'll need this script after a database import. See also Disable Triggers script.

Parameters

None.

SQL Source

rem whenever oserror exit 1
set serveroutput on size 100000
rem whenever sqlerror exit 2

set verify off
set serveroutput on

ACCEPT own PROMPT 'Enter Value for Trigger Owner : '

PROMPT
declare
   sql_stmt varchar2(1024);

cursor get_trigs is
   select distinct table_owner, table_name
   from all_triggers
   where status = 'DISABLED' and owner = UPPER('&&own');
    
trig_rec get_trigs%ROWTYPE;

begin

   FOR trig_rec IN get_trigs LOOP
     BEGIN
       sql_stmt := 'alter table '||trig_rec.table_owner
                    ||'.'||trig_rec.table_name||
                    ' enable all triggers';

      dbms_output.put_line(sql_stmt);

     EXECUTE IMMEDIATE sql_stmt;

     EXCEPTION WHEN others THEN
        dbms_output.put_line('Error Processing '
                           ||trig_rec.table_owner||'.'||trig_rec.table_name||' '
                           );
        dbms_output.put_line(SQLERRM);
     END;
   END LOOP;

end;
/

select status,count(*) from all_triggers
where owner = UPPER('&&own')
group by status;

Previous Oracle Version Links

Enable all database triggers

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal