Viewing Triggers

You can ascertain lots of information about triggers by issuing queries against the following data dictionary views:

 

DBA_TRIGGERS

All triggers in the database

 

ALL_TRIGGERS

All triggers accessible to the current user

 

USER_TRIGGERS

All triggers owned by the current user

Table 18-4 summarizes the most useful (and common) columns in these views.

Table 18-4. Useful columns in trigger views
Name Description
TRIGGER_NAME The name of the trigger
TRIGGER_TYPE The type of the trigger; you can specify: For DML triggers: BEFORE_STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, or AFTER STATEMENT. For DDL triggers: BEFORE EVENT or AFTER EVENT. For INSTEAD OF triggers: INSTEAD OF. For AFTER_SUSPEND triggers: AFTER EVENT.
TRIGGERING EVENT The event that causes the trigger to fire: For DML triggers: UPDATE, INSERT, or DELETE For DDL triggers: The DDL operation (see full list in the DDL trigger section of this chapter) For database event triggers: ERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN For INSTEAD OF triggers: INSERT, UPDATE, or DELETE For AFTER SUSPEND triggers: SUSPEND
TABLE_OWNER This column contains different information depending on the type of trigger. For DML triggers: The name of the owner of the table to which the trigger is attached. For DDL triggers: If database-wide then SYS; otherwise, the owner of the trigger For database event triggers: If database-wide then SYS; otherwise, the owner of the trigger For INSTEAD OF triggers: The owner of the view to which the trigger is attached For AFTER SUSPEND triggers: If database-wide then SYS; otherwise, the owner of the trigger
BASE_OBJECT_TYPE The type of object to which the trigger is attached. For DML triggers: TABLE For DDL triggers: SCHEMA or DATABASE For database event triggers: SCHEMA or DATABASE For INSTEAD OF triggers: VIEW For AFTER SUSPEND triggers: SCHEMA or DATABASE
TABLE_NAME For DML triggers: The name of the table the trigger is attached to. Other types of triggers: NULL
REFERENCING_NAMES For DML (row-level) triggers: The clause used to define the aliases for the OLD and NEW records For other types of triggers: The text "REFERENCING NEW AS NEW OLD AS OLD"
WHEN_CLAUSE For DML triggers: The trigger's conditional firing clause
STATUS Trigger's status (ENABLED or DISABLED)
ACTION_TYPE Indicates whether the trigger executes a call (CALL) or contains PL/SQL (PL/SQL).
TRIGGER_BODY Text of the trigger body (LONG column)