Maintaining Oracle Triggers

The following command may be helpful with triggers.

List All Triggers in a Database

To list triggers:

SELECT TRIGGERNAME FROM USER_TRIGGERS; 

Executed from Schema_owner_id

SELECT TRIGGERNAME FROM ALL_TRIGGERS; 

Executed from SYSTEM

The following data dictionary views reveal information about triggers:

• USER_TRIGGERS

      SQL> descr user_triggers;
   Name                                 Null?          Type
   -------------------------------      --------       ----
   TRIGGER_NAME                         NOT NULL       VARCHAR2(30)
   TRIGGER_TYPE                                        VARCHAR2(16)
   TRIGGERING_EVENT                                    VARCHAR2(26)
   TABLE_OWNER                          NOT NULL       VARCHAR2(30)
   TABLE_NAME                           NOT NULL       VARCHAR2(30)
   REFERENCING_NAMES                                   VARCHAR2(87)
   WHEN_CLAUSE                                         VARCHAR2(4000)
   STATUS                                              VARCHAR2(8)
   DESCRIPTION                                         VARCHAR2(4000)
   TRIGGER_BODY                                        LONG

• ALL_TRIGGERS

      SQL> desc all_triggers;
   Name                                 Null?          Type
   -------------------------------      --------       ----
   OWNER                                NOT NULL       VARCHAR2(30)
   TRIGGER_NAME                         NOT NULL       VARCHAR2(30)
   TRIGGER_TYPE                                        VARCHAR2(16)
   TRIGGERING_EVENT                                    VARCHAR2(26)
   TABLE_OWNER                          NOT NULL       VARCHAR2(30)
   TABLE_NAME                           NOT NULL       VARCHAR2(30)
   REFERENCING_NAMES                                   VARCHAR2(87)
   WHEN_CLAUSE                                         VARCHAR2(4000)
   STATUS                                              VARCHAR2(8)
   DESCRIPTION                                         VARCHAR2(4000)
   TRIGGER_BODY                                        LONG

The new column, BASE_OBJECT_TYPE, specifies whether the trigger is based on DATABASE, SCHEMA, table, or view. The old column, TABLE_NAME, is null if the base object is not table or view.

The column ACTION_TYPE specifies whether the trigger is a call type trigger or a PL/SQL trigger.

The column TRIGGER_TYPE includes two additional values: BEFORE EVENT and AFTER EVENT, which are applicable only to system events.

The column TRIGGERING_EVENT includes all system and DML events.

List the Trigger Definition

To list the trigger definition:

Select Trigger_Name,  Trigger_Body  from USER_TRIGGERS
 where Trigger_name=trigger_name;

List Trigger Information

To list trigger information:

Select Trigger_Name, Trigger_Type, Triggering_Event, Table_Owner,
Table_Name, Referencing_Names,When_Clause, Status, Description, 
Trigger_Body from USER_TRIGGERS where Trigger_name=trigger_name;

To Remove a Trigger

To remove a trigger:

drop trigger TRIGGERNAME

To Modify an Existing Trigger

On Oracle, to explicitly alter a trigger definition, use the CREATE OR REPLACE option. See a full explanation in the Oracle SQL Reference (CREATE TRIGGER).

To Disable a Trigger

By default, triggers are enabled when they're first created. Disable a trigger by using the ALTER TRIGGER statement with the DISABLE option.

For example, to disable the trigger named REORDER of the INVENTORY table, enter the following statement:

ALTER TRIGGER Reorder DISABLE;

All triggers that are associated with a table can be disabled with one statement by using the ALTER TABLE statement with the DISABLE clause and the ALL TRIGGERS option. For example, to disable all triggers that are defined for the INVENTORY table, enter the following statement:

ALTER TABLE Inventory

    DISABLE ALL TRIGGERS;