DBA_TRIGGERS

Oracle 11gR1

DBA_TRIGGERS describes all triggers in the database. Its columns are the same as those in ALL_TRIGGERS.

Related Views

· ALL_TRIGGERS describes the triggers on tables accessible to the current user. If the user has the CREATE ANY TRIGGER privilege, then this view describes all triggers in the database.

· USER_TRIGGERS describes the triggers owned by the current user. This view does not display the OWNER column.

Column	Datatype	NULL	Description
OWNER	VARCHAR2(30)		Owner of the trigger
TRIGGER_NAME	VARCHAR2(30)		Name of the trigger
TRIGGER_TYPE	VARCHAR2(16)		When the trigger fires: · BEFORE STATEMENT · BEFORE EACH ROW · AFTER STATEMENT · AFTER EACH ROW · INSTEAD OF · COMPOUND
TRIGGERING_EVENT	VARCHAR2(227)		DML, DDL, or database event that fires the trigger
TABLE_OWNER	VARCHAR2(30)		Owner of the table on which the trigger is defined
BASE_OBJECT_TYPE	VARCHAR2(16)		Base object on which the trigger is defined: · TABLE · VIEW · SCHEMA · DATABASE
TABLE_NAME	VARCHAR2(30)		If the base object type of the trigger is SCHEMA or DATABASE, then this column is NULL; if the base object type of the trigger is TABLE or VIEW, then this column indicates the table or view name on which the trigger is defined
COLUMN_NAME	VARCHAR2(4000)		Name of the nested table column (if a nested table trigger), else NULL
REFERENCING_NAMES	VARCHAR2(128)		Names used for referencing OLD and NEW column values from within the trigger
WHEN_CLAUSE	VARCHAR2(4000)		Must evaluate to TRUE for TRIGGER_BODY to execute
STATUS	VARCHAR2(8)		Indicates whether the trigger is enabled (ENABLED) or disabled (DISABLED)
DESCRIPTION	VARCHAR2(4000)		Trigger description; useful for re-creating a trigger creation statement
ACTION_TYPE	VARCHAR2(11)		Action type of the trigger body: · CALL · PL/SQL
TRIGGER_BODY	LONG		Statements executed by the trigger when it fires

Note:

1. List triggers by schema

select trigger_name

, trigger_type

, table_name

, status

from dba_triggers

where owner = '&owner'

order by status, table_name;

TRIGGER_NAME	TRIGGER_TYPE	TABLE_NAME	STATUS
SPCOL_DEL_CASCADE_50	AFTER EACH ROW	STU_TAB	ENABLED
SPCOL_DEL_CASCADE_49	AFTER EACH ROW	PAS_TAB	ENABLED
SPCOL_DEL_CASCADE_48	AFTER EACH ROW	BUS_TAB	ENABLED
SDE_LOGFILE_DELETE	AFTER EACH ROW	SDE_LOGFILES	ENABLED

2. Display all triggers on the specified table

select trigger_name

, trigger_type

, status

from dba_triggers

where owner = '&owner'

and table_name = '&table'

order by status, trigger_name;

3. Enable/Disable a trigger

alter trigger  enable

alter trigger  disable

Oracle data dictionary views

Oracle dynamic performance views