however, we the authors assume that you are using SQL*Plus. Even if you do use other tools, it is still handy to know how to perform these functions using only SQL*Plus because it is pretty much ubiquitous in the Oracle world.

Listing Triggers

There is no specific SQL*Plus command to list the triggers defined in an Oracle database. To see a list of defined triggers, you must select the information you want to see from the USER_TRIGGERS view, which is shown in Listing 11.14.

INPUT/
OUTPUT
Listing 11.14. The USER_TRIGGERS system view.

 1: DESCRIBE USER_TRIGGERS
 2:  Name                            Null?    Type
 3:  ------------------------------- -------- ----
 4:  TRIGGER_NAME                    NOT NULL VARCHAR2(30)
 5:  TRIGGER_TYPE                             VARCHAR2(16)
 6:  TRIGGERING_EVENT                         VARCHAR2(26)
 7:  TABLE_OWNER                     NOT NULL VARCHAR2(30)
 8:  TABLE_NAME                      NOT NULL VARCHAR2(30)
 9:  REFERENCING_NAMES                        VARCHAR2(87)
10:  WHEN_CLAUSE                              VARCHAR2(2000)
11:  STATUS                                   VARCHAR2(8)
12:  DESCRIPTION                              VARCHAR2(2000)
13:  TRIGGER_BODY                             LONG

ANALYSIS
USER_TRIGGERS is a system view maintained by Oracle, and it gives you access to all triggers you own. The data contained in each of the columns should be fairly obvious from the column name. The DESCRIPTION column deserves some special mention because it contains the trigger name, triggering event, trigger type, and the referencing clause in a more usable format than the other columns. I will use the DESCRIPTION column in the next section to reconstruct the SQL CREATE TRIGGER statement.

For example, to see a list of triggers defined on the employee table, issue the SQL SELECT statement shown in Listing 11.15.

INPUT/
OUTPUT
Listing 11.15. Listing all defined triggers.

 1: SELECT trigger_name, triggering_event, trigger_type
 2: FROM USER_TRIGGERS
 3: WHERE table_name = `EMPLOYEE'
 4: ORDER BY trigger_name;
 5: TRIGGER_NAME                   TRIGGERING_EVENT           TRIGGER_TYPE
 6: ------------------------------ -------------------------- ----------------
 7: emp_pay_type_chg               UPDATE                     BEFORE EACH ROW
 8: maintain_pay_history           INSERT OR UPDATE OR DELETE AFTER EACH ROW
 9: only_during_business_hours     INSERT OR UPDATE OR DELETE BEFORE STATEMENT

ANALYSIS
There are three triggers defined on the employee table. These are, of course, the three you created while reading this chapter.

Viewing Trigger Code

The PL/SQL code for a trigger is stored in the TRIGGER_BODY column of the USER_TRIGGERS view. To see the PL/SQL code for a trigger, you could just select the TRIGGER_BODY column for the trigger in which you are interested. Usually, though, it will be more meaningful to see the DDL statement used to create the trigger. The SQL*Plus code in Listing 11.16 will rebuild the CREATE TRIGGER statement for any trigger you specify.

INPUT
Listing 11.16. Commands to extract a trigger definition.

 1: SET ECHO off
 2: SET MAXDATA 50000
 3: SET LONG 50000
 4: SET LONGCHUNKSIZE 1000
 5: SET PAGESIZE 0
 6: SET HEADING off
 7: SET VERIFY off
 8: ACCEPT trigger_name CHAR PROMPT `What trigger do you want to see? `
 9: ACCEPT file_name CHAR PROMPT `Enter the output filename: `
10: SET TERMOUT off
11: SET FEEDBACK off
12: COLUMN when_clause FORMAT a60 WORD_WRAPPED
13: SPOOL &file_name
14: SELECT `CREATE OR REPLACE TRIGGER ` || description
15:   FROM USER_TRIGGERS
16:  WHERE trigger_name = UPPER(`&Trigger_Name');
17: SELECT `WHEN (` || when_clause || `)'  when_clause
18:   FROM USER_TRIGGERS
19:  WHERE trigger_name = UPPER(`&Trigger_Name')
20:    AND when_clause IS NOT NULL;
21: SELECT trigger_body
22:   FROM USER_TRIGGERS
23:  WHERE trigger_name = UPPER(`&Trigger_Name');
24: SELECT `/' FROM dual;
25: SPOOL off
26: SET TERMOUT on
27: SET FEEDBACK on
28: SET VERIFY on
29: SET HEADING on
30: SET PAGESIZE 24

ANALYSIS
In line 1, you disable the echoing of lines, which have been read from a command file to the display. Line 2 sets the maximum row length for a SQL query to 50,000 bytes. In line 3, you set the maximum size of a LONG column to be also 50,000 bytes. Lines 2 and 3 effectively limit you to retrieving triggers in which the combined length of the body and trigger specification is less than or equal to 50,000 bytes. You then set the page size to zero in line 5 in order to disable page headings, and you turn column headings off in line 6.

You don't want to clutter your reconstructed trigger with either type of heading. Line 7 tells SQL*Plus not to display statements to the terminal after substitutions have been made.

The two ACCEPT statements in lines 8_9 allow you to type in the name of the trigger that you want to retrieve and the filename in which you want to store the retrieved code. The trigger code will be retrieved to the file that you specify. The terminal output is disabled in line 10 so that you don't see the code on the screen as well. Turning feedback off keeps SQL*Plus from displaying lines telling you how many records were retrieved (line 11).

Only two commands are left before the trigger definition is retrieved. Line 12 formats the column containing the WHEN clause so that it will be a maximum of 60 characters wide. If you leave this out, the entire WHEN clause will print as one long line because the original line breaks are not preserved by Oracle when the trigger is stored. This is in contrast to the trigger body, for which Oracle does preserve the original formatting.

In line 13, the SQL*Plus SPOOL command directs output to a file, and the SELECT statements in lines 14_24 retrieve the various parts of the trigger. You turn off the file output with the SPOOL off command in line 25. In the last few lines, you return some of the session parameters to their defaults so that you will see column headings, page headings, and so on in subsequent queries.

Of course, you don't want to actually type all the commands shown in Listing 11.16 into SQL*Plus each time you want to see a trigger definition. Instead, build a file containing these commands. Be sure to save it with the .SQL extension. You can then use the SQL*Plus @ command to execute the file whenever you like. Listing 11.17 shows this command file being used to extract the definition of the maintain_pay_history trigger.

INPUT/
OUTPUT
Listing 11.17. Extracting the definition for the maintain_pay_history trigger.

1: @c:\a\list1416
2: What trigger do you want to see? maintain_pay_history
3: Enter the output filename: c:\a\list1418.sql

ANALYSIS
Line 1 shows the command to execute the command file. Line 2 shows the user being prompted for a trigger name, and line 3 shows the prompt for an output filename. In this example, the DDL to re-create the maintain_pay_history trigger will be put in the file named C:\A\LIST1418.SQL. Listing 11.18 shows the contents of this file.

INPUT/
OUTPUT
Listing 11.18. The extracted maintain_pay_history trigger definition.

 1: CREATE OR REPLACE TRIGGER maintain_pay_history
 2:   AFTER INSERT OR UPDATE OR DELETE ON employee

                                                      continues