Enable or Disable the Triggers of schema in Oracle

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';

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.