Tag Archives: check trigger on table

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;