Enabling/Disabling Triggers

Contact Us or call 1-877-932-8228
Enabling/Disabling Triggers

Enabling/Disabling Triggers

If you do not want a valid function or procedure to be available within the database, you need to drop it. Later, if you want the subprogram to be available, you need to compile the program using the original PL/SQL source code. You can render a trigger inoperable by choosing to disable it. You can enable a disabled trigger by simply setting it as enabled.

Code Sample:

Database-Triggers/Demos/fire_trigger.sql
create table x 
as
select * 
from employees
where 0=1;

drop table x;

Code Explanation

This code will fire the CREATION_ALERT trigger created earlier.

Code Sample:

Database-Triggers/Demos/disable_trigger.sql
alter trigger creation_alert disable;

select status 
from user_triggers 
where trigger_name ='CREATION_ALERT';

create table x
as 
select * 
from employees
where 0=1;

drop table x;

Code Explanation

A disabled trigger will not fire, but the code remains in the database.

Code Sample:

Database-Triggers/Demos/enable_trigger.sql
alter trigger creation_alert enable;

select status 
from user_triggers 
where trigger_name ='CREATION_ALERT';

create table x
as
select * 
from employees
where 0=1;

drop table x;

Code Explanation

A disabled trigger can be enabled without requiring the original code to be created or replaced.

Code Sample:

Database-Triggers/Demos/drop_trigger-2.sql
drop trigger creation_alert;

Code Explanation

The drop keyword is used to remove the trigger altogether. A dropped trigger cannot be enabled. It must be recreated.

Next