13.6 How do I…List information about triggers?

Problem

I want to list information about the triggers in the database. I need to determine the trigger name, the table the trigger is on, and the status of the trigger. How do I list information about triggers?

Technique

Information about triggers is contained in the USER_TRIGGERS, ALL_TRIGGERS, or DBA_TRIGGERS data dictionary views. The columns of the USER_TRIGGERS view are described in Table 13.2. The TABLE_OWNER and TABLE_NAME columns identify the table containing the triggering event. The TRIGGER_NAME column specified returns the name of the trigger. The TRIGGER_TYPE column returns the type of trigger, such as BEFORE EACH ROW or BEFORE STATEMENT. The TRIGGERING_EVENT column returns the event, such as INSERT, UPDATE, or DELETE, that causes the trigger to file. The STATUS column tells whether the trigger is currently enabled or disabled.

Table 13.2 Columns of the USER_TRIGGERS view

Column Description
TRIGGER_NAME Name of the trigger.
TRIGGER_TYPE The type of trigger, such as BEFORE ROW, AFTER ROW, BEFORE STATEMENT, AFTER STATEMENT.
TRIGGERING_EVENT Statement that fires the trigger: INSERT, UPDATE, DELETE.
TABLE_OWNER Owner of the table on which the trigger is defined.
TABLE_NAME Table on which the trigger is defined.
REFERENCING_NAMES Names used to reference OLD and NEW.
WHEN_CLAUSE WHEN clause for the trigger body to be executed on a condition.
STATUS Trigger status: ENABLED or DISABLED.
DESCRIPTION Trigger header.
TRIGGER_BODY Trigger body.

Steps

1. Run SQL*Plus and connect as the WAITE user account. If you do not have the DEPT13 table and the triggers created on it in your schema, then run CHP13_20.SQL from Step 1 of the previous How-To. The DEPT13 table is created along with the two triggers, DEPT_BI_13 and DEPT_AI_13. The next step demonstrates how a query to the USER_TRIGGERS data dictionary view can provide you with valuable information about triggers.

2. Run the CHP13_25.SQL file in SQL*Plus. The query contained in the file queries the USER_TRIGGERS table to display information about the trig-gers on the specified table. Figure 13.25 displays the results of the query for the DEPT13 table.

The COLUMN commands format the columns before running the statement. After executing the statement, provide DEPT13_31 as the substitution variable. The query displays the two triggers created in Step 1. The columns returned by the query show you the name of the trigger, when it fires, and its status. Including the TRIGGER_BODY column would also show you the code executed when the trigger fires.

The TRIGGER_NAME column specified in line 2 returns the name of the trigger. The TRIGGERING_EVENT column specified in line 3 returns the type of event that causes the trigger to fire, such as INSERT, UPDATE, or DELETE. The TRIGGER_TYPE column specified in line 4 returns the type of trigger, such as BEFORE EACH ROW or BEFORE STATEMENT. Line 5 returns the STATUS column showing whether the trigger is ENABLED or DISABLED. Including the REFERENCING_NAMES column in the query will return the REFERENCING clause if it was specified while creating the trigger. The WHEN clause for the trigger, if one exists, can be queried from the WHEN_CLAUSE column of the USER_TRIGGERS view.

How It Works

The USER_TRIGGERS, ALL_TRIGGERS, and DBA_TRIGGERS data dictionary views contain information about the database triggers in the database. The TABLE_NAME column can be used in the WHERE clause of a query to either of the views for returning information about the views contained on a specific table. Step 1 creates the sample table and two sample triggers used to display the technique. Step 2 executes a query on the USER_ TRIGGERS data dictionary view to show information about the sample triggers.

Comments

A simple query to the USER_TRIGGERS data dictionary view will provide you with valuable information about the triggers on a table. If the triggers are not behaving the way you expect, query the data dictionary to verify that they are enabled, they fire when you want, and the code is valid for the operation.

Similar to stored procedures, triggers have dependencies. They can be invalidated if a referenced object has changed and recompiled when the trigger is invoked again. Refer to How-To 10.6, which explains how you can examine the USER_DEPENDENCIES view to detect trigger dependencies. Trigger compilation errors can be queried from the USER_ERRORS view, or by issuing the SHOW ERRORS command at the SQL*Plus prompt, which will display errors for the last trigger that was compiled in the session.