Enable or Disable the Triggers in Oracle
Check triggers present in schema
col trigger_name for a20
col table_name for a20
SELECT TRIGGER_NAME,TABLE_NAME,STATUS FROM DBA_TRIGGERS;
Enable or Disable The triggers
--Enable or disable single trigger
ALTER TRIGGER owner.triggername DISABLE;
ALTER TRIGGER owner.triggername ENABLE;
--Enable or disable all trigger at table level
ALTER TABLE owner.tablename DISABLE ALL TRIGGERS;
ALTER TABLE owner.tablename ENABLE ALL TRIGGERS;
Enable or disable all trigger at schema level
- For disabling triggers of a schema
select 'ALTER TRIGGER '||OWNER||'.'||TRIGGER_NAME||' DISABLE '||';' from dba_triggers where owner='&SCHEMA_NAME';
-- Similarly for enabling
select 'ALTER TRIGGER '||OWNER||'.'||TRIGGER_NAME||' ENABLE '||';' from dba_triggers where owner='&SCHEMA_NAME';
OR
--If you login with same schema name, you also used these commands:
select 'ALTER TABLE '||table_name||' DISABLE ALL TRIGGERS;' from user_triggers;
select 'ALTER TABLE '||table_name||' ENABLE ALL TRIGGERS;' from user_triggers;
Enable or disable all trigger for a table
- For disabling triggers for a table
select 'ALTER TRIGGER '||OWNER||'.'||TRIGGER_NAME||' DISABLE '||';' from dba_triggers where table_name = ('&TABLE_NAME') and owner='&SCHEMA_NAME';
--Enable for table
select 'ALTER TRIGGER '||OWNER||'.'||TRIGGER_NAME||' ENABLE '||';' from dba_triggers where table_name = ('&TABLE_NAME') and owner='&SCHEMA_NAME';