Check Enable and Disable Triggers in Oracle

Enabling and Disabling Triggers in Oracle

In simple language trigger is a task which executed and linked with other task.
If one task linked with other task then on execution of first task then link task(i.e trigger) is also executed.

Database triggers are act like procedures that are stored in the database and fired on specific condition. Database trigger executed with DML, DDL and database event like shutdown startup etc.
Example you create trigger on insert of employee table whenever the insert operation performed on employee table associated trigger is fired and run its procedure.

Note: By default on creating trigger it is enabled.

Create syntax of trigger

CREATE TRIGGER scott.emp_history_changes
AFTER
DELETE OR INSERT OR UPDATE
ON scott.emp
.
.
.
pl/sql block

Enable the Triggers

-- Enable trigger by name
ALTER TRIGGER trigger_name ENABLE;

— IF table having more than one trigger
ALTER TABLE table_name ENABLE ALL TRIGGERS;

Disable the trigger

-- disable trigger by name
ALTER TRIGGER trigger_name DISABLE;

— Disable all trigger for the table
ALTER TABLE table_name DISABLE ALL TRIGGERS;

Check trigger information

select owner,trigger_name,table_name,status from dba_triggers;

Advertisements

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.