Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

10.6 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.

Example 10-10 shows a script that will describe a
single trigger in detail. The script's output is a
CREATE TRIGGER statement that may be used to re-create the trigger.
The FOLD_AFTER option is used in the COLUMN commands to force each
column to begin a new line of output. SET PAGESIZE 0 gets rid of any
page titles and column headings that would otherwise clutter the
output. The trigger_body column is of type LONG,
so SET LONG 5000 ensures that you'll see at least
the first 5000 bytes of a trigger body. Use a higher value if your
triggers are longer than that.

Example 10-10. A script to generate a CREATE TRIGGER statement

SET VERIFY OFF
SET LONG 5000
SET PAGESIZE 0
COLUMN create_stmt FOLD_AFTER
COLUMN description FOLD_AFTER
COLUMN when_clause FOLD_AFTER
SELECT 'CREATE OR REPLACE TRIGGER ' create_stmt,
description,
CASE WHEN when_clause IS NOT NULL THEN
'WHEN (' || when_clause || ')'
ELSE
''
END when_clause,
trigger_body
FROM all_triggers
WHERE owner = UPPER('&owner')
AND trigger_name = UPPER('&trigger_name');
SET PAGESIZE 14 The following invocation of Example 10-10 shows the
definition for the trigger emp_delete_check:

SQL> @ex10-10
Enter value for owner: gennick
Enter value for trigger_name: emp_delete_check
CREATE OR REPLACE TRIGGER
emp_delete_check
BEFORE DELETE ON employee
FOR EACH ROW
BEGIN
IF (:OLD.employee_termination_date IS NULL)
OR (:OLD.employee_termination_date >= TRUNC(SYSDATE)+1) THEN
RAISE_APPLICATION_ERROR (-20001,
'You must terminate an employee before deleting his record.');
END IF;
END;

This output contains a blank line in front of the BEGIN keyword. That
blank line is where the WHEN clause would go, if one had been defined
when the trigger was created. You can execute this output to
re-create the trigger. To do that, you could simply copy and paste
the output into SQL*Plus, taking care to terminate the block (a
trigger is a PL/SQL block) with a forward slash
(/) on a line by itself. (Chapter 2 shows examples of PL/SQL block
execution.)