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), triggering_event (e.g., INSERT OR UPDATE), and table_name (e.g., employee ).

trigger_body

Code that executes when the trigger is fired .

when_clause

Any WHEN clause that restricts the conditions on which the trigger executes.

owner

Owner of the trigger. Triggers are usually owned by their table's owners but that doesn't have to be the case.

trigger_name

Name of the trigger.

referencing_names

Alias names through which the trigger body references old and new columns.

 

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

     



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151
Simiral book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net