SQL Script Enable All Database Triggers

Updated: 01-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 Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 

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') and base_object_type='TABLE';
    
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;

Related Links

Oracle 8: (SQL Script) Enable All Database Triggers

Return to Index of SQL Scripts

Site Map

General Information : Home | Company Profile | Contact Us | Legal
Database Support : Proactive Support | Pay Per Incident Support
Expert Services : High Availability | Creative Solutions | Training
Build Services : Oracle Installations | Revive your Oracle Setup | Low Cost Development
Free Oracle Resources: DBA Scripts | Oracle Links