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 |
---|---|
| Combination of |
Get Oracle SQL*Plus: The Definitive Guide, 2nd Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.