Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Triggers

Information about triggers can be retrieved from two views, the all_triggers view and the all_trigger_cols view. Most of the time you will find all the information you need in all_triggers. The all_trigger_cols view contains a list of all database columns referenced in the trigger. This view is sometimes useful when you are troubleshooting because it can show you which triggers reference or modify any given database column.

To find out whether any triggers have been defined on a table, query all_triggers as shown in Example 10-9.

Example 10-9. Listing the names of triggers on a table

SET VERIFY OFF
COLUMN description FORMAT A40 WORD_WRAPPED
COLUMN status FORMAT A10

SELECT description, status
FROM all_triggers
WHERE table_owner = UPPER('&owner')
  AND table_name = UPPER('&table_name');

The following run of Example 10-9 lists the triggers defined on the employee table:

SQL> @ex10-9
Enter value for owner: gennick
Enter value for table_name: employee

DESCRIPTION                              STATUS
---------------------------------------- ----------
emp_hire_date_check                      ENABLED
BEFORE INSERT OR UPDATE ON employee
FOR EACH ROW

emp_delete_check                         ENABLED
BEFORE DELETE ON employee
FOR EACH ROW

Table 10-3 describes the columns returned by the query in Example 10-9, as well as other important columns you can look at to understand a given trigger more fully.

Table 10-3. The key columns in the view

Column	Description
description	Combination of trigger_name (e.g., emp_hire_date_check), trigger_type (e.g., BEFORE EACH ROW), ...